Is it Possible for Excel to select Column Chart Data Source Dynamically??

[Kingkoopa]

Hey Guys,

Currently using windows 10 and Excel 2010.

Is there a way for excel to select the data source for Column Charts Dynamically?

Example :

[TABLE="width: 393"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Australia[/TD]
[TD]Vietnam[/TD]
[TD]Indonesia[/TD]
[TD]Mexico[/TD]
[/TR]
[TR]
[TD]Quarter 1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Variance 1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Quarter 2[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Variance 2[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Quarter 2 vs Quarter 1[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

If I have a drop down list. That has Quarter 1, Quarter 2 and Summary.

1) If I chose Quarter 1 then graph would select Quarter 1 & Variance 1 data.
2) If I chose Quarter 2 then graph would select Quarter 2 & Variance 2 data.
3) If I chose Summary then graph would select Quarter 1 & Quarter 2 & Quarter 2 vs Quarter 1 data.

Currrently I can do 1) & 2) no issues by using vlookup to change the data output when different drop down list is selected. Problem arises when I try to include 3) as now I need to select 3 different rows of data.

Any idea guys, how I can incorporate them into a single dynamic graph? . Sorry if it sounds confusing.

Thank you

 

[JazzSP8]

If you're doing this how I think you are then you could add another data source that resolves to NA() unless your drop down box is equal to Q1 vs Q2?

=IF([DropDown]<>Q1vQ2,NA(),VLOOKUP(Etc. Etc.)

The #NA values would simply not be plotted on your Graph until the VLOOKUP populates?