How do I know if Dynamic Range is working?

I believe you should be using something like this to define your Dynamic Named Range
=OFFSET(Sheet1!$B$1,1,0,COUNTA(Sheet1!$B:$B)-1,1)

You can test by
• Press F5
• Type the name of your Dynamic Named Range
• Press OK

Your DNR will be selected.

Does that help?

You can open up the name manager box by selecting ctrl + F3

Once in there you name the dynamic name range and in the refer to you enter the formula.

=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B),1)

Save it by pressing the check box and then if you place your cursor in the box with the formula it should hightlight the range with the marching ants

test with
=sum(OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B),1))
now put some numbers in b2 down
mind you i'd use

=Sheet1!$B$2:INDEX(Sheet1!$B:$B,COUNTA(Sheet1!$B:$B))
instead

It is...Control + F3 opens the name manager box

or

You can press F3 and it opens the paste name box so this way you know the exact spelling.

look in name manager on the formula tab

Originally Posted by livemusic

I don't know the correct way to do it yet because so far, I have 3 answers and 3 opinions! Thanks to all. Question... I did the F5 thing and yes, it did highlight my range. My data is in B2 to B15. It highlighted B2 to B16 (added one blank cell). Is that correct... because it is allowing for new data to be entered?

Second question... is there no way to even know if this dynamic range exists unless you just remember it? I mean, I can press F5 and do that IF I remember the name of the dynamic named range. But what if I totally forget about it? Seems it should be displayed somewhere.

If you look at the replacement formula I posted, you'll see that it:
• Starts with the title cell
• Offsets by 1 row
• Uses a height equal to the count of non-blank cells...minus 1 (to compensate for the title cell)

Typically, I name my dynamic named ranges with a "DNR" prefix to make the easier to find.
example: DNR_NameList

Another method to help find named ranges (Using Excel 2007 or later) is to create a list of all named ranges and their definitions:
• Select a blank cell with nothing beneath it
• Formulas.Use_in_formula.Paste_Names...Click: Paste List

Does that help?

Hopefully this example sheet helps a little.

Getting an error. Here is what I have done.

1. Created a Workbook, 'wells-and-doi.xlsx'

2. Created a Dynamic Range, 'WellNames,' in Column B. Confirmed that it exists by pressing F5 and entering the name and yes, it highlighted the range.

3. Created another Workbook, call it Workbook2. In a certain column, I will want to enter a well name by selecting it from a Drop Down list, which will be populated by the names of the wells from the Dynamic Range in the other workbook, mentioned above.

In this Workbook2, I created a named range by doing this...

On the Ribbon, clicked the Formulas tab, then clicked Define Name.
Typed a name for the List, 'WellNamesList'
From the Scope drop down, selected Workbook
In the refers to box, typed:

=wells-and-doi.xlsx!WellNames

Clicked OK

Got it from here...
http://blog.contextures.com/archives...#ixzz1R0UIIkj8

4. Both workbooks are open. In Workbook2, I did this, something else I found online...

Create the Dropdown List
- Select the cells in which data validation will be set.
- On the Ribbon, click the Data tab, then click Data Validation
- In the Allow box, choose List
- In the Source box, type an equal sign, then the list name, e.g.: =MyCustList
- Click OK

So, in that box, I typed '=WellNamesList.'

===

When I do the last step above, it says evalutates to an error." I can't find the error.

So, to recap, I have the first workbook (wells-and-doi.xlsx) and it contains the Dynamic Named Range 'WellNames.' I have the second work, Workbook2, which has the named range 'WellNamesList.' This named range is pointed to wells-and-doi.xlsx!WellNames. I then created a drop down list, to be populated by the named range 'WellNamesList.'