Dynamic Chart Help pls

[Cantonalives]

Hi All,
I have used offsets to chart data. This data uses N/A# where blank or zero so it does not plot.

Data runs Jan to Dec. The above N/A# process works well when the N/A# comes late in the year. But does not work if the N/A# is at the start of range OR if there are gaps in the data - for instance, blanks in Jan, numbers mid year and then blanks (N/A#) at the end of the year.

Sample of the offset I am using.
=offset('Test1'!$b$9,0='test1'!$b$9:$an$9,0,1,count('test1'!$b$9:$b$9:$an$9))

Any help is much appreciated...


Thanks - CL.

 

[Hui]

The formula in Red
=offset('Test1'!$b$9,0='test1'!$b$9:$an$9,0,1,count('test1'!$b$9:$b$9:$an$9))

Will return values of True/False

But the Offset function is expecting a value for the Row Offset in that position

Can you please post a sample file with some data so we can see what you want

 

[Cantonalives]

Please see attached.

Item in yellow and hence error on chart.

Not sure if a line can start and stop in this manner... However once started, it's likely to be continual until stopping permanently.

Thanks for any advice..

CL

 

[Hui]

Your file is reporting as a corrupted file ?

 

[Hui]

Test 1 should be : =OFFSET('Summary Data'!$B$5,0,0,1,12)
Date should be =OFFSET('Summary Data'!$B$2,0,0,1,12)

 

[Cantonalives]

Thanks for the reply Hui,
But those are static formulas that do not expand. Set for a width of 12 - right?

=OFFSET('Summary Data'!$B$5,0,0,1,COUNT('Summary Data'!$B$5:$B$5:'Summary Data'!$AN$5))

The above would capture the first N/A#s, but it does not expand accordingly.

Thanks for looking.. Any more advice greatly appreciated.

-CL

 

[Hui]

Why not link it to the dates
And only add dates each month

=OFFSET('Summary Data'!$B$5,0,0,1,COUNT('Summary Data'!$B$2:$ZZ$2))

 

[Cantonalives]

Thanks Hui. That did not work, but did when I used 'counta' instead of 'count'.


Thanks,

-CL