Dynamic Chart Question

[Guest]

I want to define a chart label and I have this:

=OFFSET(CPI_SPI_PITD!$B$33,0,0,1,MIN(CPI_SPI_PITD!CPI_SPI_PITD_chtlen,COUNTA(CPI_SPI_PITD!$33:$33)-1))

In row 33 of this sheet, I have the following Information
A33: CUM
B33: Jan 05
C33: Feb 05
D33: Mar 05 ...
M33ec 05

CPI_SPI_PITD_chtlen is defined as 12 for now
When I add data in column N, and view the named range that applies to this,
it still shows from B33: M33. Never moves to the right. What am I missing?

Thanks,
Barb Reinhardt

 

[Andy Pope]

Hi,

Try this,

=OFFSET(CPI_SPI_PITD!$B$33,0,MAX(0,COUNTA(CPI_SPI_PITD!$33:$33)-CPI_SPI_PITD!$A$29-1),1,CPI_SPI_PITD!$A$29)

You current have the column offset set to zero and the number of columns
being determined by a COUNTA formula. On need to calculate the offset
and use the width directly form the CPI_SPI_PITD_chtlen value.

Cheers
Andy

 

[Barb Reinhardt]

Andy, I don't get where you got

CPI_SPI_PITD!$A$29-1

A29 has nothing in it. I want to use the chtlen value which currently has
12 in it.

There is other information in that cell

 

[Andy Pope]

A29 was where I put the value 12. replace A29 with your named range
CPI_SPI_PITD_chtlen.

Andy, I don't get where you got

CPI_SPI_PITD!$A$29-1

A29 has nothing in it. I want to use the chtlen value which currently has
12 in it.

There is other information in that cell