help with setting up dynamic name ranges

J

Jeff

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,
 
J

Jeff

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,
 
D

Debra Dalgleish

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)
 
J

Jeff

Debra,
The problem with subtracting the header rows is... not all header rows
contain data. e.g. b2 to b4 could be empty...

Thanks,
 
D

Debra Dalgleish

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