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.