# Thread: Simplying mulitple graphs (2000/2003)

1. ## Simplying mulitple graphs (2000/2003)

I need to make a spreadsheet with 25 graphs, each with a dynamic range (using the Offset function). I would like to create the first graph and copy and paste the rest, but...I can't figure out how to set up the offsets so that each graph automatically use a different range of cells. Is this possible? I'd like to store my data on one tab and have the graphs appear on another.

Thanks!

Larry

2. ## Re: Simplying mulitple graphs (2000/2003)

Have a look at this example and the steps to create it. Its called a Changing Range Chart, where you can have 1 chart that can display the 25 types of data to plot...(obviously if the context allows!)

Step:
Changing Range Chart

Here is a great trick that will allow you to change your chart range by selecting it from a list.

1. On sheet1, in cell A1 type the heading Amounts, in B1 type Prices, in C1 type Ages, in D1 type Months.
2. Under Months put the 12 months of the year.
3. Under the other headings type some values.
4. Click in cell F1 and go to Data>Validation. Select List from the Allow: box.
5. In the Source box type: =\$A\$1:\$C\$1.
6. Click OK.
7. In cell G1 put this formula: =IF(F1=A1,0,IF(F1=B1,1,IF(F1=C1,2,0))). This will make the default (if F1 is blank) Amounts.
8. Go to Insert>Name>Define and type the name MyColumns in the Names in workbook box.
9. In the Refers to box type the formula: =OFFSET(\$A\$2,0,\$G\$1,COUNTA(\$D\$2:\$D\$13),1) and click OK.
10. Click the Chart wizard from the Standard toolbar, select Line from the Chart types, and choose any type from the Chart sub-type.
11. Click Next and then the Series tab.
12. Click Add, select cell F1 using the collapse dialog to the left of the Name box or type: =Sheet1!\$F\$1.
13. In the Values box type: =Sheet1!MyColumns and in the Category (x) axis labels type =Sheet1!\$D\$2:\$D\$13
14. Step through the rest of the Wizard setting up the chart how you want, but for the Chart location choose As object in
15. Click Finish.

Position and size your chart so you can select from your list in cell F1.

Now select a name from your list in F1 and your chart series range will change accordingly.

I can provide a sample Workbook to show practically how this works. Just ask for it if you need it and I will dig it out of my archives!

3. ## Re: Simplying mulitple graphs (2000/2003)

To answer >>> I would like to create the first graph and copy and paste the rest, but...I can't figure out how to set up the offsets so that each graph automatically use a different range of cells.

You will need 25 dynamic name ranges, each one refering to the new set of data. You can then copy the chart and for each pasted chart you must set up the data series of that chart to point to a different dynamic name range of the 25 you pre-set up! I hope you understand what I'm getting at!

4. ## Re: Simplying mulitple graphs (2000/2003)

Why don't you just create 1 chart and use a dynamic range to allow choosing which range to set?

With this scheme you only need a name for each of the ranges in the chart. You can have a combobox (eg) choose the offset column from the data and thus choose the column to plot. From 1 chart and a combobox box, you might be able to have the user select and view the "25 charts".

This has the advantage, if the chart needs to be updated all can be updated at once since there is only 1 chart...

If you posted some example data, we might be able to come up with a demo for you...

Steve

5. ## Re: Simplying mulitple graphs (2000/2003)

Hi Ikman,
Here is the sample file. I found it after some digging around. Its a simplified example of a changing range chart. This is the result of following the steps I posted in my first reply!

6. ## Re: Simplying mulitple graphs (2000/2003)

Steve,

Thanks - that's a good idea, but I need to display 25 different data sets - it's a dashboard of key indicators for our company.

Larry

7. ## Re: Simplying mulitple graphs (2000/2003)

Rudi - thank you very much - the dropdown will simplify assigning the data to each graph. The examples makes it much easier. I was hoping to get around creating 25 range names - actually, I'll need 50 because I expect the X-axis value to be dynamic as well (we'll only display the most recent 12-18 months of data), but this will work well. Thanks again!

Larry

8. ## Re: Simplying mulitple graphs (2000/2003)

That sound like a bit of grafting to get 50 named ranges up and running. Good luck with your endeavours.

9. ## Re: Simplying mulitple graphs (2000/2003)

That's even easier. Use a dropdown list to define the range name(s) for what you want graphed.

10. ## Re: Simplying mulitple graphs (2000/2003)

Interesting thought - does the drop down need the file name included? Can I use the value directly from the dropdown or do I need to do something with it first?

Thanks!!!

Larry

11. ## Re: Simplying mulitple graphs (2000/2003)

I was thinking in terms of using a Combo Box from the Controls Tool Box. You would then fill the combo box with a list of chart names (whatever you want to call the charts). Then in the combo box change event routine you would define the names required by the chart. I don't understand what you mean by "does the drop down need the file name included." You would use the value from the combo box in a Select Case statement to know how to define the range names for the chart.

12. ## Re: Simplying mulitple graphs (2000/2003)

ooooohhhhhhh...........haven't assigned range names from VBA before - will have to investigate that.

Thanks!

Larry

13. ## Re: Simplying mulitple graphs (2000/2003)

What do you mean by "different datasets"?

If the chart setup is identical and only the data changes for the various charts, it is an ideal candidate for creating 1 chart from many data sets.

If the charts do not look the same, that is an entirely different situation.

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•