Dynamic range using partial column?
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. |
Dynamic range using partial column?
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. |
All times are GMT +1. The time now is 01:33 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com