I seem to be missing something regarding named ranges. I have entered data in
column A, inserted a name that refers to:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

But when I add data to the end of the column, the range doesn't expand.

Help!
--
Thanks!

Dee

Is the sheet named Sheet1?
In the InsertNameDefine dialog box, select the range name in the list.
Click in the Refers To box
What is selected on the worksheet?

dee wrote:
I seem to be missing something regarding named ranges. I have entered data in
column A, inserted a name that refers to:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

But when I add data to the end of the column, the range doesn't expand.

Help!


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

Yes, the Sheet is named Sheet1 and I did what you suggested. It actually
shows that the named range did expand.

However, when I refer to the named range in, say a Match function, it
returns #NA error message for cells that are in the "expanded" range, but
works fine for those in the original range.


--
Thanks!

Dee


"Debra Dalgleish" wrote:

Is the sheet named Sheet1?
In the InsertNameDefine dialog box, select the range name in the list.
Click in the Refers To box
What is selected on the worksheet?

dee wrote:
I seem to be missing something regarding named ranges. I have entered data in
column A, inserted a name that refers to:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

But when I add data to the end of the column, the range doesn't expand.

Help!


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

I apologize profusely! I did as you instructed to check the range. I then
realized I had a colon after what I was trying to match and didn't have one
in the list - and had used False... that was the problem.

I will research this further to understand it more clearly, for example, why
this named range doesn't appear in the Name box drop-down.

If you could point me in the right direction, it would be appreciated.

Again, I apologize.
--
Thanks!

Dee


"Debra Dalgleish" wrote:

Is the sheet named Sheet1?
In the InsertNameDefine dialog box, select the range name in the list.
Click in the Refers To box
What is selected on the worksheet?

dee wrote:
I seem to be missing something regarding named ranges. I have entered data in
column A, inserted a name that refers to:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

But when I add data to the end of the column, the range doesn't expand.

Help!


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

No problem, and thanks for reporting how you solved the mystery.

Names for dynamic ranges don't appear in the Name box dropdown list.
However, if you're creating a formula, and want to see a list of range
names, you can press the F3 key on the keyboard, to open the Paste Name
dialog box.

dee wrote:
I apologize profusely! I did as you instructed to check the range. I then
realized I had a colon after what I was trying to match and didn't have one
in the list - and had used False... that was the problem.

I will research this further to understand it more clearly, for example, why
this named range doesn't appear in the Name box drop-down.

If you could point me in the right direction, it would be appreciated.

Again, I apologize.


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

I found that shortcut key earlier today - thanks so much.

I don't totally understand this - I guess what exactly a dynamic range is -
so will research it further to understand why the name doesn't appear in the
name box.

Thank you for your patience!


--
Thanks!

Dee


"Debra Dalgleish" wrote:

No problem, and thanks for reporting how you solved the mystery.

Names for dynamic ranges don't appear in the Name box dropdown list.
However, if you're creating a formula, and want to see a list of range
names, you can press the F3 key on the keyboard, to open the Paste Name
dialog box.

dee wrote:
I apologize profusely! I did as you instructed to check the range. I then
realized I had a colon after what I was trying to match and didn't have one
in the list - and had used False... that was the problem.

I will research this further to understand it more clearly, for example, why
this named range doesn't appear in the Name box drop-down.

If you could point me in the right direction, it would be appreciated.

Again, I apologize.


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

In the name box, if you type in the name of the defined range, the range
will be displayed

--
Don Guillett
SalesAid Software

"Debra Dalgleish" wrote in message
...
No problem, and thanks for reporting how you solved the mystery.

Names for dynamic ranges don't appear in the Name box dropdown list.
However, if you're creating a formula, and want to see a list of range
names, you can press the F3 key on the keyboard, to open the Paste Name
dialog box.

dee wrote:
I apologize profusely! I did as you instructed to check the range. I
then realized I had a colon after what I was trying to match and didn't
have one in the list - and had used False... that was the problem.

I will research this further to understand it more clearly, for example,
why this named range doesn't appear in the Name box drop-down.

If you could point me in the right direction, it would be appreciated.

Again, I apologize.


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

Hello, Debra - I hope you still post to this site. I had a question about
this same issue. I have successfully followed your formula
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) for creating a dynamic range
name. Everything worked perfectly, except that that I want my range to be
$A$2 (in my worsheet, $A$1 is a column header that I do not want to include
in my drop-down list; my data starts in $A$2). However, when I change the
range in the formula to $A$2, my list shows from the bottom up and has blanks
and I have to scroll up to get to the top of my list. Is there any way to
change this so that my range in the formula is $A$2 and my list shows from
the top down?

I greatly appreciate your help.


"Debra Dalgleish" wrote:

Is the sheet named Sheet1?
In the InsertNameDefine dialog box, select the range name in the list.
Click in the Refers To box
What is selected on the worksheet?

dee wrote:
I seem to be missing something regarding named ranges. I have entered data in
column A, inserted a name that refers to:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

But when I add data to the end of the column, the range doesn't expand.

Help!


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

try
=OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1,1)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"MsBeverlee" wrote in message
...
Hello, Debra - I hope you still post to this site. I had a question about
this same issue. I have successfully followed your formula
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) for creating a dynamic
range
name. Everything worked perfectly, except that that I want my range to be
$A$2 (in my worsheet, $A$1 is a column header that I do not want to
include
in my drop-down list; my data starts in $A$2). However, when I change the
range in the formula to $A$2, my list shows from the bottom up and has
blanks
and I have to scroll up to get to the top of my list. Is there any way to
change this so that my range in the formula is $A$2 and my list shows from
the top down?

I greatly appreciate your help.


"Debra Dalgleish" wrote:

Is the sheet named Sheet1?
In the InsertNameDefine dialog box, select the range name in the list.
Click in the Refers To box
What is selected on the worksheet?

dee wrote:
I seem to be missing something regarding named ranges. I have entered
data in
column A, inserted a name that refers to:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

But when I add data to the end of the column, the range doesn't expand.

Help!


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