Results 1 to 13 of 13
  1. #1
    Lounger
    Join Date
    Dec 2005
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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!
    Regards,
    Rudi

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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!
    Regards,
    Rudi

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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!
    Regards,
    Rudi

  6. #6
    Lounger
    Join Date
    Dec 2005
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #7
    Lounger
    Join Date
    Dec 2005
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    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.
    Regards,
    Rudi

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Legare Coleman

  10. #10
    Lounger
    Join Date
    Dec 2005
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Legare Coleman

  12. #12
    Lounger
    Join Date
    Dec 2005
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simplying mulitple graphs (2000/2003)

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

    Thanks!

    Larry

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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
  •