PDA

View Full Version : Sleeper: Multiple Charts with dynamic ranges



jmenche
06-08-2005, 07:45 PM
Hello,

I frequently analyze data with charts and have to copy the same chart over and over again...select the new range...yada yada yada.

The problem is that the ranges are not the same height (# rows).

For example:

Category Segment # of Items Dollars
Gum Fun 30 1000
Gum Fun 40 2500
Gum Fun 45 3000
Gum Mild 20 1200
Gum Mild 18 1700

Assuming that the list continues and the values for Category change...How can I create the same chart (let's say a column (dollars) and line (items) chart) for each "range"??

I have limited programming knowledge (but a good imagination). I was thinking that one way could be to loop through the list and identify and then name each range. The second part would be to loop through the ranges and create the charts.

Any help would be greatly appreciated.

Jacob Hilderbrand
06-14-2005, 10:09 PM
You can certainly loop through the range and create charts specific to each range. Can you post your workbook so we can have a closer look at your data and what you want to accomplish?

Aaron Blood
06-15-2005, 09:46 AM
Hello,

I frequently analyze data with charts and have to copy the same chart over and over again...select the new range...yada yada yada.

The problem is that the ranges are not the same height (# rows).

For example:

Category Segment # of Items Dollars
Gum Fun 30 1000
Gum Fun 40 2500
Gum Fun 45 3000
Gum Mild 20 1200
Gum Mild 18 1700

Assuming that the list continues and the values for Category change...How can I create the same chart (let's say a column (dollars) and line (items) chart) for each "range"??

I have limited programming knowledge (but a good imagination). I was thinking that one way could be to loop through the list and identify and then name each range. The second part would be to loop through the ranges and create the charts.

Any help would be greatly appreciated.

...or perhaps you only need one chart.

Maybe think about a macro that would either:
A) Cycle thru the ranges and produce an image of the chart referencing the range.
B) Create one chart that includes all ranges as seperate series, then use a simple autofilter to toggle hide/show just the series you want.

Sometimes better to not have 100's of chart objects stored in your app if you can help it.

mdmackillop
06-15-2005, 10:38 AM
Hi jmenche,
Welcome to VBAX.
If you want to post data, click on the Go Advanced button below the message box, and click on the Manage Attachments button. You will need to zip your excel file in order to send it. Please remove any "sensitive" data, email addresses and the like.
Regards
MD