Problem resolved... had to create a whole series of NAMED ranges and use formulas such as ....

=OFFSET('FTE Count'!$E$6,,0,1,COUNTA('FTE Count'!$E$6:$AD$6))



On Wednesday, 23 January 2013 09:32:27 UTC+8, Steven North wrote:
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.