Hi,

I'm trying to create a dynamic range (to accommodate various numbers of
elements) but the selection of cells I want to use is not in a column by
itself.

This means I can't use:

=offset(Sheet1!$a$1,0,0,counta(sheet1!$a:$a)-1,1)

because the counta() figures out all the entries in the A column, even
though all I want it to return is the contiguous block of cells from, say, A2
to A23. There is stuff in the cells below A23 so that seems to throw it off.

Any ideas on how to fix this or get around it?

TIA.

Hi,

=offset(Sheet1!$a$1,0,0,counta(offset(sheet1!$a$1, 0,0,50,1),1)

it works for 50 line

Thanks,
--
Farhad Hodjat


"Donal P" wrote:

Hi,

I'm trying to create a dynamic range (to accommodate various numbers of
elements) but the selection of cells I want to use is not in a column by
itself.

This means I can't use:

=offset(Sheet1!$a$1,0,0,counta(sheet1!$a:$a)-1,1)

because the counta() figures out all the entries in the A column, even
though all I want it to return is the contiguous block of cells from, say, A2
to A23. There is stuff in the cells below A23 so that seems to throw it off.

Any ideas on how to fix this or get around it?

TIA.