Dynamic charting based on two drop down selections - help

**TalResha** · 01-06-2011, 10:00 AM

Hi Guys,

As you can see in the attached there is a sheet called dashboard.

You will see 6 white boxes that will be charts, and a drop down menu with choices ranging from Daily, Weekly, Monthly.

Now, I need to Chart the graphs based on Name (the drop down box at the top), and the period (daily, weekly, monthly) to pull from the Master Data sheet.

In the Master Data sheet, there are three tables, the first one being daily, the second to the left is weekly (calculating values based on daily) and the one farthest to the left is monhtly.

Can you please help me with my charting difficulties?

Thank you in advance!

**Andy Pope** · 01-06-2011, 12:45 PM

You do not say what your difficulties are.

Try searching forum for Dynamic ranges

**TalResha** · 01-06-2011, 12:49 PM

Difficulties are that I do not know how to make the chart update based on my drop downs.

I have tried searching for a solution to this, but being new to dynamic charting I have no idea how I can solve this problem.

Thank you for your quick reply however, and please jump in if you know a solution to this.

**Andy Pope** · 01-06-2011, 12:53 PM

Here is a link on dynamic charting
http://peltiertech.com/Excel/Charts/Dynamics.html

You have not provided any information on what the 6 charts should show.
I understand the category labels will be daily/weekly/monthly but what columns in each of the tables are you wanting to report?

**TalResha** · 01-06-2011, 12:58 PM

Hi,

I have visited that site, but still not quite sure how I can make it work for my purposes.

The Columns I need for daily would be E, F in one chart, G,H in the next chart etc.

For weekly it would be W,X i the first chart, Y,Z in the second chart, etc.

For monhtly it would be AO in the first and AP in the next, etc.

I hope it makes more sense now.

**TalResha** · 01-07-2011, 05:11 AM

Anything guys, I really am truly stuck on this one, please if you know how to help me I would really appreciate it!

**Andy Pope** · 01-07-2011, 06:12 AM

The attached show the start of a solution. You will need to understand how dynamic ranges work otherwise you will not be able to extend the solution.

I have defined the fixed ranges for the Daily, Weekly and Monthly category labels. Which one to use is based on the result of a CHOOSE function.

The data named ranges are simply offset from the column used for labels. This does present a problem with your current data layout as you have hidden columns between labels and data columns in 2 of the 3 tables. Ideally you would reposition those columns at the right of the table so when the named ranges CHTDATA_1 and CHTDATA_2 are used they reference valid data for all tables.

**TalResha** · 01-07-2011, 06:52 AM

Hi Andy,

Thank you very much for this example, as well as the explanation as to how this would need to be setup in order to work with this logic.

This will help me greatly as I now see approximately how to proceed with this solution.

If I am able to solve this fully I will close this thread.

Thank you once again for your expertise!