Hi All,
I'm having an issue with a dashboard I am creating. The dashboard in question is an order intake dashboard, including a number of combo boxes that act as filters for the data.
These combo boxes are linked (via VBA) to a sheet containing the current selection of each of the combo boxes.
The data staging sheet that feeds the main dashboard sheet uses a number of SUMIFS to gather the information from the main data table.
Up until here everything works fine. VBA, functions, data all working as expected.
I have created a line chart in the dashboard displaying some of this data.
Here my problems start...
If I change the filter for the year(s) being displayed it works fine. This filter works using VBA to essentially rewrite the data table (as changing this filter can result in differing length tables of data).
If I change any other filter, the data table updates properly, but I cannot get the chart to update without changing the year and changing it back again (rewriting the table).
These filters all change the value in the linked cell and nothing else. So I change the filter, which updates the cell, which changes the result of the SUMIFS formulas and SHOULD update the chart... but it doesn't.
I've tried all manner of things:
- Data -> Refresh all
- Changing an element of the chart and changing it back
- Changing an element of the data table feeding it
- Converting (via VBA) all the formulas to values (I did this by accident - I was aiming to simulate rewriting the table with formulas, either way it didn't work)
Anyone have any other ideas? Or perhaps a new approach to the problem? I'm starting to think I'm asking Excel for too much =/
Gary
I'm having an issue with a dashboard I am creating. The dashboard in question is an order intake dashboard, including a number of combo boxes that act as filters for the data.
These combo boxes are linked (via VBA) to a sheet containing the current selection of each of the combo boxes.
The data staging sheet that feeds the main dashboard sheet uses a number of SUMIFS to gather the information from the main data table.
Up until here everything works fine. VBA, functions, data all working as expected.
I have created a line chart in the dashboard displaying some of this data.
Here my problems start...
If I change the filter for the year(s) being displayed it works fine. This filter works using VBA to essentially rewrite the data table (as changing this filter can result in differing length tables of data).
If I change any other filter, the data table updates properly, but I cannot get the chart to update without changing the year and changing it back again (rewriting the table).
These filters all change the value in the linked cell and nothing else. So I change the filter, which updates the cell, which changes the result of the SUMIFS formulas and SHOULD update the chart... but it doesn't.
I've tried all manner of things:
- Data -> Refresh all
- Changing an element of the chart and changing it back
- Changing an element of the data table feeding it
- Converting (via VBA) all the formulas to values (I did this by accident - I was aiming to simulate rewriting the table with formulas, either way it didn't work)
Anyone have any other ideas? Or perhaps a new approach to the problem? I'm starting to think I'm asking Excel for too much =/
Gary
