help with setting up dynamic name ranges

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,
Please help me with defining a name range using offset.
I need to set up a name per column using dynamic range. Each Column
starts at row 7. Data are appended to row (and it will grow and I don't know
how many rows before hand, and the column does not contain empty cell.


Thanks,
 
Gord,
Thanks for answering, but that didn't exactly solve my problem. The
example
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
works only when range starts from column 1. However, I'll need the formula
which would starts from row 7 (row 1-6 maybe blank or with data). There
isn't a way for my to set the height. Just wondering if there is a generic
formula for that. Say, counting for row7 til the end of the row (of the
column).

Thanks,
 
The formula is an example that you can adapt to your workbook. For
example, to create a dynamic range in column B, change the references,
and subtract the count of items in the header rows:

=OFFSET(Sheet1!$B$7,0,0,COUNTA(Sheet1!$B:$B)
-COUNTA(Sheet1!$B$1:$B$6),1)
 
Debra,
The problem with subtracting the header rows is... not all header rows
contain data. e.g. b2 to b4 could be empty...

Thanks,
 
The formula doesn't subtract the header rows. It subtracts the count of
items in the header rows. Did you try the formula?
 
Back
Top