Dynamic Named Ranges do not show up in the "Name Box"

pecosvil

Board Regular
Joined
Apr 13, 2010
Messages
52
I just created a Dynamic Defined Named Range following this references:
http://www.ozgrid.com/Excel/DynamicRanges.htm
http://support.microsoft.com/kb/830287

It works perfectly, except for the fact that the Named Range is not appearing in the "Name Box". The curious thing is that the named range is working fine. For example, if I created a Dynamic Named Range named "Currencies" and I go to the "Name Box" and type "Currencies" it will automatically select the appropriate range. But if I just go to the "Name Box" and try to find the range "Currencies" I won't be able to find it.

Anyone experienced anything similar? Any ideas of what's happening or how can I solve it?

Many thanks in advance!
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
Dynamic ranges don't appear in the names box, never have to my recollection.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,176
I just created a Dynamic Defined Named Range following this references:
http://www.ozgrid.com/Excel/DynamicRanges.htm
http://support.microsoft.com/kb/830287

It works perfectly, except for the fact that the Named Range is not appearing in the "Name Box". The curious thing is that the named range is working fine. For example, if I created a Dynamic Named Range named "Currencies" and I go to the "Name Box" and type "Currencies" it will automatically select the appropriate range. But if I just go to the "Name Box" and try to find the range "Currencies" I won't be able to find it.

Anyone experienced anything similar? Any ideas of what's happening or how can I solve it?

Many thanks in advance!
Try...

Insert | Name | Define

on Excel versions prior to 2007

or

Formulas | Name Manager
 

pecosvil

Board Regular
Joined
Apr 13, 2010
Messages
52
Aladin, in Formulas--> Name Manager the List and its name appear. That's not the problem.

ArkusM, you are right. If I hit F3 they appear. But I want it to appear in the Name Box. This spreadsheet will be used by other users, and I want them to be able to find things easily (even easier than going to Formulas-->Name Manager).

I also try using INDEX instead of using OFFSET (following this thread http://www.mrexcel.com/forum/showthread.php?t=311117 ) but I still have the same problem.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
From http://www.mrexcel.com/forum/showpost.php?p=2767759&postcount=2

Nope, dynamic named ranges will not appear in the named range list..
From http://www.contextures.com/xlnames01.html

Note: Dynamic named ranges will not appear in the Name Box dropdown list. However, you can type the Excel names in the Name Box, to select the range on the worksheet.
From http://www.xl-central.com/create-dynamic-named-range.html

Note that a dynamic named range will not appear in the Name Box dropdown list. However, you can type the name in the Name Box to select the range on the worksheet.
 

pecosvil

Board Regular
Joined
Apr 13, 2010
Messages
52
Thanks shg! I had read some of the references you sent. Still, I don't understand why it doesn't appear in the name box. My first thought was that because OFFSET is volatile, Excel could not define the range until it is called. But I wasn't convinced of that, and anyway I tried with INDEX and it has the same problem. It seems to be something so basic... if it appears in the Name Manager, why not in the Name Box?

Probably I won't find a solution, but maybe finding a reason will improve our understanding of the Excel logic behind this (wishful thinking?)
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
I don't know of any particular reason they don't appear, except perhaps to avoid the overhead of calculating them when selected.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,346
As an alternative (or a workaround, if you like), create the named ranges in VBA.

Such as, and it will depend on exactly what you're doing, on an event like worksheet deactivation. This can work well if you have the lists on some worksheets and use them elsewhere.

Code:
'you might give the header cell a defined name,
'so that references to it are always known
 
Private Sub Worksheet_Deactivate()
 
with me.range("hdrWhatever").currentregion
'adjust range to exclude header from named range
.offset(1).resize(.rows.count -1 ).name = "MyName"
end with
 
end sub
 

pecosvil

Board Regular
Joined
Apr 13, 2010
Messages
52
Thanks Fazza!
I thought about that option, but I don't think it will work fine in the current project. I am basically generating a template. The user will input some information (ie, currencies, countries, etc.) in one sheet (the "Lists" sheet). That sheet will feed the Name Ranges, and those in turn will feed other sheets (for example, Data sheet) where the user will have drop down lists with list-data validation based on the info she input on Lists sheet.

If I understand correctly your suggestion, it wil require the user to populate the Lists sheet, then run the Subroutine (that will generate the named ranges), and then go and fill the information in Data sheet. Although it's very simple, I want the user to interact the least possible with Subroutines (ie, probably they will not run the subroutine and input any information... the data validation won't be working fine since the List is empty and it will allow any value, if I recall well)

Thank you all for your help, and let me know if you have other thoughts!
 

Watch MrExcel Video

Forum statistics

Threads
1,098,844
Messages
5,465,039
Members
406,409
Latest member
windiestboat

This Week's Hot Topics

Top