Dymanic Range Names

[dee]

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]

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

 

[dee]

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.

 

[dee]

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]

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]

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!

 

[Don Guillett]

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

 

[MsBeverlee]

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.

 

[Don Guillett]

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