mark-labarge
asked on
Excel Dynamic Graph Series
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.
=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.
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much. This will be a tremendous amount of work, but worth it in the long run.
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,Control
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,MyWorkb
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