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

Barb Reinhardt wrote:
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





--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

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" wrote in message
...
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 wrote:
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





--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

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

Barb Reinhardt wrote:
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" wrote in message
...

Hi,

Try this,

=OFFSET(CPI_SPI_PITD!$B$33,0,MAX(0,COUNTA(CPI_SP I_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 wrote:

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

=OFFSET(CPI_SPI_PITD!$B$33,0,0,1,MIN(CPI_SPI_PI TD!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





--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info




--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info