Solved

Excel Dynamic Graph Series

Posted on 2011-02-12
5
560 Views
Last Modified: 2012-05-11
I have an Excel Workbook with numerous graphs. An example of a graph series is below:
=SERIES("Gross Revenue with Fed Taxes",'2 Yr Rev with Taxes'!$C$67:$Z$67,'2 Yr Rev with Taxes'!$C$78:$O$78,1)

the $C$78:$O$78 above is the range for the graph where $O$78  is the end of the graph line for January and will need to be changed to $P$78 for February , and so on. I have been making this change manually and would greatly appreciate a way to have the reference automatically change based on a month number that is entered on a control sheet. Thanks in advance for any assistance.
0
Comment
Question by:mark-labarge
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 34881417
Hello,

Assuming your control sheet is called ControlSheet and the number for the number of months to display in the chart is in A1, create a range name called chtData, referencing the following formula:

=offset('2 Yr Rev with Taxes'!$C$78,0,0,1,ControlSheet!$A$1)

Then edit the chart series through the dialog box and enter this in the series values field

'2 Yr Rev with Taxes'!chtData

Alternatively, edit the chart series and change it to

=SERIES("Gross Revenue with Fed Taxes",'2 Yr Rev with Taxes'!$C$67:$Z$67,MyWorkbook.xls!chtData,1)

Change MyWorkbook.xls in the above to the name of your workbook.

For more information on dynamic charts, see the tutorials on Jon Peltier's site http://peltiertech.com/Excel/Charts/Dynamics.html

cheers, teylyn
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 34881429
see attached for a working example.

cheers, teylyn
myWorkbook.xls
0
 

Author Comment

by:mark-labarge
ID: 34887582
Thank you very much this is very helpful. I don't think I gave you enough information. I have 4, regional workbooks. Each workbook has 10 worksheets with a graph and each graph has several data series with data coming from different rows. The columns are always the same, with a starting point at C and and an ending point that  changes each month. In my series above that only thing that changes is the Z but each workbook might hve 40 or 50 different series
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 500 total points
ID: 34891015
Hello,

you will need to create range names for each data series and plug them into the chart.

This is a bit of work to set up initially, but after that it will save a lot of time.

Whenever I have several series in a dynamic chart, and the source data is in several rows, but the same columns, I define one range name as the base range name and the other range names based on that range, using Offset()

For example

chtSeriesA refers to =offset('2 Yr Rev with Taxes'!$C$78,0,0,1,ControlSheet!$A$1)
chtSeriesB refers to =offset(chtSeriesA,2,0)
chtSeriesC refers to =offset(chtSeriesA,5,0)

chtSeriesA defines a range in row 78. chtSeriesB is two rows below that, chtSeriesC is 5 rows below chtSeriesA.

There are several ways to define the dynamic ranges, but in order to create dynamic charts, you will need dynamic range names for each chart series.

cheers, teylyn
0
 

Author Closing Comment

by:mark-labarge
ID: 34892619
Thank you very much. This will be a tremendous amount of work, but worth it in the long run.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question