help with setting up dynamic name ranges
 

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,

help with setting up dynamic name ranges
 

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,

help with setting up dynamic name ranges
 

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,

help with setting up dynamic name ranges
 

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

help with setting up dynamic name ranges
 

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

Thanks,



"Debra Dalgleish" wrote:

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

help with setting up dynamic name ranges
 

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

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

Thanks,



"Debra Dalgleish" wrote:


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





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