Create charts with dynamic ranges

[edwardj3]

Hi forum,

Hoping you guys can assist and resolve this issue I've had for a while. Every month I receive data with three columns. Col1 is hourly segments so a maximum number of rows of 672 for 28 days, 696 for 29 days, 720 for a 30 day month and 744 for a 31 day month. There may occasions when a hour segment is missing if there is an issue in collecting the data.

I need to create 5 charts using Col 1 (dd/mm/yyyy hh:mm format with 2 spaces between data and time) and Col2 for each week in the month with each week starting on the Mondays in the month e.g.
week 1 01/08/2019 00:00 to 04/08/2019 23:00 (4 days)
week 2 05/08/2019 00:00 to 11/08/2019 23:00 (7 days)
week 3 12/08/2019 00:00 to 18/08/2019 23:00 (7 days)
week 4 19/08/2019 00:00 to 25/08/2019 23:00 (7 days)
week 5 26/08/2019 00:00 to 31/08/2019 23:00 (6 days)

I need to create another 5 charts using Col 1 and Col3 for each week in the month with each week starting on the Mondays. Ranges as above.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Col1[/TD]
[TD]col2[/TD]
[TD]Col3[/TD]
[/TR]
[TR]
[TD]01/08/2019 00:00[/TD]
[TD]2345[/TD]
[TD]343[/TD]
[/TR]
[TR]
[TD]01/08/2019 01:00[/TD]
[TD]4355[/TD]
[TD]543[/TD]
[/TR]
[TR]
[TD]01/08/2019 02:00[/TD]
[TD]4436[/TD]
[TD]654[/TD]
[/TR]
[TR]
[TD]01/08/2019 04:00[/TD]
[TD]4443[/TD]
[TD]456[/TD]
[/TR]
[TR]
[TD]01/08/2019 05:00[/TD]
[TD]7557[/TD]
[TD]643[/TD]
[/TR]
[TR]
[TD]01/08/2019 06:00[/TD]
[TD]4346[/TD]
[TD]435[/TD]
[/TR]
[TR]
[TD]down to [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31/08/2019 23:00[/TD]
[TD]4665[/TD]
[TD]469[/TD]
[/TR]
</tbody>[/TABLE]

This data is received monthly so I want to remove the need to hard code the cell ranges required to create the 5 separate charts. How do you add dynamic ranges to create charts? Is there another solution? Is VBA required?

Advice on how I could do this would be appreciated.

 

[Dave Paradi]

You do not need VBA, you just need to create a chart that has dynamically defined ranges. This is the second time in just over a week that I have seen a question about defining a dynamic range chart where the user or formulas can select the data for the chart. So I wrote up a method to do this and have just published it here: https://www.thinkoutsidetheslide.co...l-chart-with-a-dynamic-range-that-you-select/. I hope that helps you solve this issue.

 

[edwardj3]

Dave,
I've applied your solution to my data for 1 week and the dynamic chart works which is fantastic. This will save me quite a bit of time when I need to produce monthly. I can probably also apply this to my other work.
Thank you for the clear and detailed guidance as it will allow others on my team to also follow and utilise.