Dynamic range using partial column?

G

Guest

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.
 
G

Guest

Hi,

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

it works for 50 line

Thanks,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top