Making a Single Dynamic Chart

[Deepak Sharma]

Hello All,

I have prepared three charts on one sheet i.e. Yearly view & Quarterly & Yearly View for all years.

Actually, I need to combine all these charts in a Single chart, where I can view as per my choice whether it is yearly, monthly or Quarterly (For a Single Year) AND one option should be there to view it for ALL The Years.

Is it possible? Please advise.

Thanks & Regards,

 

[Chihiro]

You can leverage Camera tool.

You will need 2 level Named Range. Since, you can't use INDIRECT in Camera Tool formula bar.

Top level - Call it Select_View or some such.
=INDIRECT("v"&Sheet2!$I$1)

Then 3 child level named ranges. vMonth, vQuarter & vYear.
Each referring to range containing the chart.

The Camera Tool will refer to top level (i.e. =Select_View)

See attached sample (sheet2).

Edit: Oh and I changed Sales!B1 & I1 to refer to Sheet2!$F$1 using formula. To control both chart using single drop down for year.

Edit2:
Just noticed that your chart title isn't dynamic.

To make it dynamic just use some out of way cell to construct chart title and use that cell as reference cell for the title.

Ex: in Sales!D1
="Quarterly Unit Sales " & B1

In formula bar for the chart.
=Sales!$D$1