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)

Jeff wrote:
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,

"Gord Dibben" wrote:


Jeff

See Debra Dalgleish's site for instructions.

http://www.contextures.on.ca/xlNames01.html#Dynamic


Gord Dibben MS Excel MVP

On Thu, 21 Sep 2006 16:36:02 -0700, Jeff wrote:


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,





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html