Hello -

I need to create a 'dynamic' chart, with plotted data being 'chosen' by
the user. Let's use this example:
Say my company sells 9 models of shoes: Model1, Model2....Model9.
There are 3 categories of shoes: Sports, Mens, Womens. Further, we
have many stores selling the shoes and they can be grouped several
ways: Total (ie North America); by Country (us or ca), by Region (ie
US-East), or simply by Store (ie #210). There is only piece of
information available for each shoe: number of pairs in stock, but this
data is available on a weekly level.

Managers want to be able to see how stock levels have developed over
time, but need to be able to see such development on multiple levels
(ie by store, by model, by country, etc). My thought was to create
drop-down boxes for each of the 'levels' of data, with users selecting
the view they wanted and the graph updating automatically. The source
data is a raw (ie not pivoted) worksheet, with each row containing ALL
of the data pieces above (Country, Region,Store#, model, etc).

But how do I use the drop-down box selections to modify the source
data, and thus the graph? The series-name should also change to
reflect the chosen options. OR, maybe you can suggest another way to
do what I need...?

Any/all help is GREATLY appreciated!

Rgds, Ray

Hi Ray,

Easy way - use a pivot chart

Harder - use VBA - there are a few samples at
http://www.edferrero.com/charting.aspx that should get you started - see
'Chart Selector'

Ed Ferrero
Microsoft Excel MVP
http://www.edferrero.com


Hello -

I need to create a 'dynamic' chart, with plotted data being 'chosen' by
the user. Let's use this example:
Say my company sells 9 models of shoes: Model1, Model2....Model9.
There are 3 categories of shoes: Sports, Mens, Womens. Further, we
have many stores selling the shoes and they can be grouped several
ways: Total (ie North America); by Country (us or ca), by Region (ie
US-East), or simply by Store (ie #210). There is only piece of
information available for each shoe: number of pairs in stock, but this
data is available on a weekly level.

Managers want to be able to see how stock levels have developed over
time, but need to be able to see such development on multiple levels
(ie by store, by model, by country, etc). My thought was to create
drop-down boxes for each of the 'levels' of data, with users selecting
the view they wanted and the graph updating automatically. The source
data is a raw (ie not pivoted) worksheet, with each row containing ALL
of the data pieces above (Country, Region,Store#, model, etc).

But how do I use the drop-down box selections to modify the source
data, and thus the graph? The series-name should also change to
reflect the chosen options. OR, maybe you can suggest another way to
do what I need...?

Any/all help is GREATLY appreciated!

Rgds, Ray

Excellent! I've never used a PivotChart before, but a quick
run-through looks promising. I've also bookmarked your link below --
some great solutions!

Thanks alot, Ed....

Rgds, Ray


Ed Ferrero wrote:
Hi Ray,

Easy way - use a pivot chart

Harder - use VBA - there are a few samples at
http://www.edferrero.com/charting.aspx that should get you started - see
'Chart Selector'

Ed Ferrero
Microsoft Excel MVP
http://www.edferrero.com


Hello -

I need to create a 'dynamic' chart, with plotted data being 'chosen' by
the user. Let's use this example:
Say my company sells 9 models of shoes: Model1, Model2....Model9.
There are 3 categories of shoes: Sports, Mens, Womens. Further, we
have many stores selling the shoes and they can be grouped several
ways: Total (ie North America); by Country (us or ca), by Region (ie
US-East), or simply by Store (ie #210). There is only piece of
information available for each shoe: number of pairs in stock, but this
data is available on a weekly level.

Managers want to be able to see how stock levels have developed over
time, but need to be able to see such development on multiple levels
(ie by store, by model, by country, etc). My thought was to create
drop-down boxes for each of the 'levels' of data, with users selecting
the view they wanted and the graph updating automatically. The source
data is a raw (ie not pivoted) worksheet, with each row containing ALL
of the data pieces above (Country, Region,Store#, model, etc).

But how do I use the drop-down box selections to modify the source
data, and thus the graph? The series-name should also change to
reflect the chosen options. OR, maybe you can suggest another way to
do what I need...?

Any/all help is GREATLY appreciated!

Rgds, Ray