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: 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 |
Dynamic Chart Question
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 |
Dynamic Chart Question
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 |
Dynamic Chart Question
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 |
All times are GMT +1. The time now is 03:37 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com