Working around for Always One Selected Value on multi-sheet project
I'm trying to figure out a way to use Always One Selected Value on one sheet on a project, but have that particular value ignored on other sheets.
Here's what I have going on: Sheet #1 list project performance data across Business Units (some ratios on build time, days overdue, cost, defect counts, etc). This sheet also color codes each data point as Red/Amber/Green based on annual benchmarks that have been set. So, we only allow users to set the value for Project year to a single value and set our RAG coloring accordingly. When multiple years are selected, it just gets confusing & ugly.
Sheet #2 is a new sheet I'm making now. Sheet #2 tracks defect details (% of defects by phase found in, % by root cause, % by severity, etc). I'd like to be able ot use Sheet 2 to do trend analysis looking at the defects across several years for a Business Unit. I thought I'd be cute and create a second year value in my load to use on this sheet (loading the Project Year again and just calling it defect year). However, I apparently cannot select multiple defect years on Sheet #2, as my data only includes one year's worth of selecoitn since I'm forcing one project year on Sheet #1. When I display my current selections, Sheet #2 always shows Project Year. I've set Clear all values on entry and no dice.
I've seen similar topics mention using macros, but we're using AJAX and users are primarily accessing the project via mobile device.
Given my constraints, are there any creative (or ordinary) solutions?
So, I'd like to somehow have Sheet 2 ignore the selections of Sheet 1, or at least override the
One ordinary solution would be to add a calculation condition to the chart(s) on sheet #1 that says to only render the chart if a single year is selected. Just put your year field as the calculation condition, since anything non-zero is true, while null (multiple values) should be false. Then change the message for the calculation condition to something like "Please select a single year to see this chart".
Another option would be to use set analysis on sheet #1 and have all charts display the maximum year possible. That way at least the users always see something, and a little text box could explain what's going on. So instead of sum(Sales), for instance, you'd use something like this: