Example file attached.
I would like to create a dynamic chart control which allows the user to select certain criteria within cells and the chart will display the results of the selected combination. I know that this is exactly the sort of thing that a pivot table is for, but as this will be used as a dashboard by people other than myself, unfortunately I do not have the time (or patience!) to teach them all how to use pivot tables.
In the example I have a table of data, a 'Chart control' section and the chart itself. In reality the data is held on it's own sheet and contains many more rows, columns and potential values. The chart itself will actually be the same source information for both the Title and the horizontal axis - just filtered based on the control section - but the vertical axis should be able to change it's source column.
So the hierarchy will always be...
SiteWeek No.Category x
The Chart Title will always the be 'Site' column.
The horizontal axis will always be the 'Week No.' column.
The vertical axis will always one of the 'Category' columns.
So I just need to be able to filter which values are totaled within the Title and horizonal axis, but also select which column is totaled within the vertical axis.
If the 'Site' selector field is blank, I would like this to mean that ALL values are totaled.
Similarly, if either of the 'Week No.' selector fields are blank then I would like this to mean that the values totaled are the earliest and latest values respectively.
Also, possibly, if the 'Category' selector field is blank then I could maybe default to totaling the 'Category 0' column.
Potentially, I may also include a date range selector either in place of, or as an alternative to, the Week No. selector. These would be mutually exclusive so only a Week No. range or Date range would be used at any one time - I imagine I would need to add an additional field to just specify which of the ranges the chart should use.
Finally, please ignore the totals I have used on the example chart, they are just placeholders and not the actual totals I would expect.
Any help on the best approach to this would be appreciated, this is normally something I would take the time to sit down and puzzle out myself, but timescales are against me on this one.