Graph Auto-Update with Dynamic Date Range

[Shawn1888]

Hello,

I'm trying to create an injury tracker where a custom Location, Contractor, Start Date and End Date can be selected and a number of graphs will automatically update according to the selection. I've attached a small example file that works similarly to my full file, but my full file is going to look at a number of years of data while this sample file only has a few months.

I've successfully laid out the file so that the graph data is calculated correctly (Dashboard Data sheet) and I can get it so a graph automatically updates correctly for a short time, but it seems that anytime I save and close the file, when I open it up the next time the date range won't automatically update and shows the entire maximum possible date range. What I'm hoping I can learn from the Excel community is how to better create a dynamic graph like this so it always reflects the selected date range.

To explain how I'm creating the graph, I have injuries in the Injury worksheet from 1/1/2021 - 8/14/2021. In the Dashboard Data worksheet, I have a couple checks in columns L - W to see if the data exists and then columns Y - AC only pull data that meet the selected criteria on the Dashboard sheet. To create the graph, I enter the maximum date range on the Dashboard worksheet, then create a bar chart with Y2:AC10 selected. When I then change the selection on the Dashboard sheet, it works properly. However, it eventually reverts to the full max date range, even if data only exists for the selected date range from the Dashboard sheet.

[josephteh]

May I suggest using PivotTable? First, I have converted your data in Injury sheet to Table so that the PivotTable range can expand automatically when new data is input.

Using Filters, you can filter data by Injury Location and Contractor. To filter Date, you can either do the filtering in the PivotTable itself or via Timeline (if this feature is available in your Excel version).

Remember to refresh your PivotTable whenever new data is updated. Right-click anywhere in your PivotTable and select Refresh.

I have selected Refresh data when opening the file > Right-click anywhere in PivotTable > PivotTable Options > Data > Refresh data when opening the file.

P/s: Please update your profile if your current Excel version is not Excel 2007.