Dynamic Chart Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

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 ...
M33:Dec 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
 
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
 
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
 
A29 was where I put the value 12. replace A29 with your named range
CPI_SPI_PITD_chtlen.

Barb said:
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
Click to expand...
 
Back
Top