Quote:
Originally Posted by Steven North[_2_]
On Wednesday, 23 January 2013 09:28:44 UTC+8, Steven North wrote:
I've been reading up on Dynamic Charts and have come across a slight challenge that I am trying to figure out.
I have 12 charts running off the same data table (using different rows) as I am charting FTE per division for the current financial year.
We're currently up to pay period 15 out of a possible 26, thus I have 11 pay periods that are 0.
What I am hoping to achieve is whether there is another way rather than using named ranges and OFFSET for it to chart what I want it to automatically.
One idea that i was trying to achieve was using OFFSET within the SERIES formula based on the number of periods I want to chart.
For example: CELL A1 = PERIOD 14 (named PERIOD)
Current formula for Series 1: =SERIES('FTE Count'!$D$31,,'FTE Count'!$E$31:$AD$31,2)
The idea I had for Series 1: =SERIES('FTE Count'!$D$31,,'FTE Count'!$E$31:OFFSET($E$31,,PERIOD),2)
the idea is that if i have the data for period 15, I tell the charts to chart period 1 to 15. Rather than charting all 26 periods and charting 0
I've also tried this...
=SERIES('FTE Count'!$D$31,,OFFSET($E$31,,'FTE Count'!$A$1),2)
Excel advises that the formula is not valid.
|
Have a look at the link below for details on dynamic charts.
http://m.techrepublic.com/blog/msoff...-in-excel/7836
Personally I'd use INDEX to create the dynamic named ranges rather than OFFSET as it's not volatile in the same way OFFSET is and therefore will cause less slowdown of your workbook.
Details on using INDEX for dynamic ranges can be found about half way down the page that this link goes to.
http://www.excelhero.com/blog/2011/0...ing-index.html
Let me know if you need more help.
S.