Dynamic Charting with START and END selections

[Space_Crime]

Hello All,

I have a sample file attached with a simple single column monthly data and added data validation to select the start and end month so I can chart the selected range.
I have created ranged names for the data but unable to get the chart working properly. I am stuck at trying to find the data series in charting working as it is frustrating.


Thanks in advance.

Jay

[AliGW]

Welcome to the forum!

What I do for this sort of thing is create a dynamic table containing the required data, then use this as the chart's source. I then hide the dynamic table under the chart object.

I have created the following table in the attachment:

Excel 2016 (Windows) 32 bit

	G	H
4	Month	Value
5	Feb	9
6	Mar	8
7	Apr	7
8	May	10
9	Jun	7
10	Jul	10
11	Aug	5
12	Sep	4

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	G	H
5	=IF(OR(G4="",G4=E$3),"",INDEX($A$2:$A$13,MATCH(E$2,$A$2:$A$13,0)+ROWS(G$5:G5)-1))	=IFERROR(VLOOKUP(G5,$A$2:$B$13,2,0),"")

Sheet: Sheet1

Change the parameters in your coloured cells to see the chart change.

[Space_Crime]

Hello Ali,

Thanks for this approach, however when selecting some parameters, the chart is not showing the desired range. For ex. Jan-Sep, Apr-Dec, or Feb-Oct.
On your charting source table, the output works flawlessly but is in the actual chart some of those ranges don't appear correctly.

Jay.

[AliGW]

OK - I will have another look in the morning. Probably a simple fix.

Check the data that has been selected for the chart - it’s probably just a case of making sure that it’s 13 rows deep.

[AliGW]

Yes - that’s all you need to do. Right-click the chart and choose Select Data. In the dialog change the source range at the top to G5:H16. Now the chart will work correctly.

[Space_Crime]

Yes, that fixed it. Perfect, thanks.

[AliGW]

No problem.