Dynamic Chart Question

[sahafi]

Hi All,

I'm trying to set up line charts for more than 40 different production
locations. But I would like to give the user the option of selecting one or
two (at most) locations then have Excel display the corresponding chart/s
instead of creating 40+ charts. Each location will have its own data table
(pounds/dollars). How do I go about doing this task?

Thanks.

 

[Andy Pope]

Hi,

For information on dynamic charts and controlling which series are
displayed have a look at Jon's page.
http://peltiertech.com/Excel/Charts/Dynamics.html

Cheers
Andy

 

[sahafi]

Thanks, but I don't see what i'm looking for on that page (may be dynamic is
not the right word). What I need is to let the user select which location to
see its chart. Once the user select the desired location the chart for the
location will be populated on the chart window (only the chart for that
location). I'm trying to save memory and time by not creating 40 different
sheets with 40 different charts on the same file. May be a VBA would help.
Like having a procedure/function that once the location get selected, the
code will look for corresponding location's data table and plot the line
chart. Any help is greatly appreciated.

Thanks.

 

[Andy Pope]

Ok, have a look at Ed's version of dynamic charts. The dropdown control
allows you to pick which row or column is charted.

Chart Selector Version 2
http://www.edferrero.com/charting.aspx

Cheers
Andy

 

[Tushar Mehta]

Thanks, but I don't see what i'm looking for on that page (may be dynamic is
not the right word). What I need is to let the user select which location to
see its chart. Once the user select the desired location the chart for the
location will be populated on the chart window (only the chart for that
location). I'm trying to save memory and time by not creating 40 different
sheets with 40 different charts on the same file. May be a VBA would help.
Like having a procedure/function that once the location get selected, the
code will look for corresponding location's data table and plot the line
chart. Any help is greatly appreciated.

Thanks.

An example of what you want would be to combine example #4 with example #6
in
Dynamic Charts
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[sahafi]

Thanks for the reply. No. 6 is good for selecting desired location, but i'm
not sure if No. 4 will help me. Here's my delima: The data that i'm using to
chart each location (about 40 loc), spans across 60 columns and about 64 rows
for each location! Out of the calculations and analyses on those 60x64, will
have five columns to graph 5 lines for each chart for each location. The
charts will have weeks (52) on the X axis while the weight and sales data
will be plotted on the primary Y and and secondary Y axes. As you can see
it's a bit complex to merge all these data into one area/table as it is in
your examples. The designe/structure of the models for all locations looks
the same but the content/data vary by location.

Any guidance on this will be highly appreciated.

Thanks.