Dymanic Range Names

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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?
 
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.
 
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.
 
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.
 
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!
 
In the name box, if you type in the name of the defined range, the range
will be displayed
 
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.
 
Back
Top