Offset function problem-Dynamic range

[Guest]

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.

 

[Franz Verga]

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