Thread: Dynamic Chart Question

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: ***
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: ***
> 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" <andy@andypope.info> wrote in message
news:uYmimR7GGHA.1728@TK2MSFTNGP09.phx.gbl...
> 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: ***
>> 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" <andy@andypope.info> wrote in message
> news:uYmimR7GGHA.1728@TK2MSFTNGP09.phx.gbl...
>
>>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: ***
>>>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