Offset function problem-Dynamic range
I have used the offset function many times before to name a dynamic range
however this time something funny is happening. I have a sheet with many different data tables that that I will use for charting. When I am trying to define a range for one series the using the offset function: =OFFSET('Chart Data'!$J$5,0,0,COUNTA('Chart Data'!$J:$J),1) it selects a range of cells from J5 to J33, even though cells J13:J21 are blank. Does anyone know why its doing this? I was able to find a correction to this by entering in the offset function as follows: =OFFSET('Chart Data'!$J$5,0,0,COUNTA('Chart Data'!$J:$J)-21,1) but I am worried that as more data is inserted into this table I might lose something. |
Offset function problem-Dynamic range
MarkM wrote:
I have used the offset function many times before to name a dynamic range however this time something funny is happening. I have a sheet with many different data tables that that I will use for charting. When I am trying to define a range for one series the using the offset function: =OFFSET('Chart Data'!$J$5,0,0,COUNTA('Chart Data'!$J:$J),1) it selects a range of cells from J5 to J33, even though cells J13:J21 are blank. Does anyone know why it's doing this? I was able to find a correction to this by entering in the offset function as follows: =OFFSET('Chart Data'!$J$5,0,0,COUNTA('Chart Data'!$J:$J)-21,1) but I am worried that as more data is inserted into this table I might lose something. Hi Mark. Probably in the range J13:J21 there are some blanks (i.e. null strings or spaces or formulas that give a blank). Maybe you can solve using COUNT function instead of COUNTA... -- (I'm not sure of names of menus, options and commands, because translating from the Italian version of Excel...) Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
All times are GMT +1. The time now is 01:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com