Greenbriars
Member
Hi
I have been trying to teach my daughter some more advanced Excel techniques than she learned in school and she suggested using some of her exercise data to provide a real life example.
Her thinking was a dynamic chart , but I thought that we could be more creative, which is where I perhaps fell down. Without understanding all the consequences I chose to create a dashboard with my daughter's required chart, but with other data and crucially a timeline to select the time period analysed.
I enter the daily data in the "Data" tab, which also contains a cell for the daily target. The "Chart" tab contains a pivot table based on the data in the "Data" tab together with various calculations to generate the data for the dashboard. The pivot table is updated automatically when the data changes via a macro. I've had to use a workaround by creating a range called "Steps" which will allow 1,500 data points because I don't think there is a way to treat a pivot table column in the same way as a table.
The main chart is a combination chart with the actual data displayed in bars and a target and period average displayed as lines. This works fine, but if I select a time period which contains no data and then reselect a period with data, the chart type changes to a bar chart.
Question 1: Is there any way I can fix the format (type) of the chart even if the timeline has called for no data?
Question 2: Can I analyse a pivot table in the same way as a table? i.e. referring to the pivot table and a column in a formula.
I attach a copy of the spreadsheet.
I have been trying to teach my daughter some more advanced Excel techniques than she learned in school and she suggested using some of her exercise data to provide a real life example.
Her thinking was a dynamic chart , but I thought that we could be more creative, which is where I perhaps fell down. Without understanding all the consequences I chose to create a dashboard with my daughter's required chart, but with other data and crucially a timeline to select the time period analysed.
I enter the daily data in the "Data" tab, which also contains a cell for the daily target. The "Chart" tab contains a pivot table based on the data in the "Data" tab together with various calculations to generate the data for the dashboard. The pivot table is updated automatically when the data changes via a macro. I've had to use a workaround by creating a range called "Steps" which will allow 1,500 data points because I don't think there is a way to treat a pivot table column in the same way as a table.
The main chart is a combination chart with the actual data displayed in bars and a target and period average displayed as lines. This works fine, but if I select a time period which contains no data and then reselect a period with data, the chart type changes to a bar chart.
Question 1: Is there any way I can fix the format (type) of the chart even if the timeline has called for no data?
Question 2: Can I analyse a pivot table in the same way as a table? i.e. referring to the pivot table and a column in a formula.
I attach a copy of the spreadsheet.