Need help with a chart featuring dynamic date ranges and intervals

[OzoSil]

Hi Team,

I have a doosy one here if anyone is up for a challenge.

Im just working on a spreadsheet where daily stats can be entered, the daily stats cover every weekday and runs horizontally as below (random dummy data used):

Stats1.jpg

So what I essentially want to do is in the stats worksheet, two dates can be selected (start and end) and essentially it would plot the sales for the selected date range on the graph.

The other thing I want to do (only if possible) that the period to graph can also be selected, for example if 'show period' is set to 'weekly' instead of daily it would sum 5 days and only plot each week between the selected periods.

Stats2.jpg

So far, I almost got the dynamic date range to work with the following function in the 'Stats' worksheet:

Please Login or Register  to view this content.

However once I created in the Name Manager, It does not let me use in the 'Axis label range' of the graph.

Any help is truly appropriated, example file attached.

[Andy Pope]

Try changing the Named range formula.

=INDEX( '2022'!$C$1:$CA$1,1,MATCH(Stats!$C$3, '2022'!$C$1:$CA$1,0)):INDEX( '2022'!$C$1:$CA$1,1,MATCH(Stats!$F$3, '2022'!$C$1:$CA$1,0))

[OzoSil]

Thank you that worked!

Anyone know if its possible to change the chart to plot from daily to weekly?

[Andy Pope]

You will need to manipulate the data on the sheet, via formula, to create weekly data. Then chart that.

The chart will not summarise the data for you.

When you have 2 sets of data you can either plot the required data in a 3rd set of data for charting.
Or modify your named ranges to choose which data to plot.