Chart with dynamically adjusting chart area

Try following the below steps on your example file. Once the setup below is done you only need to remember step 10 to refresh.
It doesn't make things automatic but it is easier than altering your chart range every day. Might be other ways to does this and will be keeping an eye out for any other solutions myself in case a simpler method is available.


Instructions below are also provided on the attached file which has been modified already.

1. Add column with formula to determine if row should be included in chart (see attached which has this in place)
2. convert the data area to an Excel Table
do this by highlighting the whole area D4:I32 then hit Ctrl+T to convert to a table (make sure you tick the box to use first row as headers)
3. with any cell in the table selected head to Data>From Table/Range
4. A new window will appear with a copy of your table in view. Next the the heading name click on the little icon to change the data type.
5. Right-Click on the Values heading and choose replace errors. type 'null' and click ok
6. Filter the Include? column to Y only. This is you chart data range
7. Close the window and save and changes (keep). a new table will appear on a new sheet in excel.
8. Apply date formats to the required columns.
9. Create your chart from this new table.
10. When the original table is updated you just right-click on the new table and choose refresh (or from the Ribbon: Data>Refresh)
The chart should update