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

[pecosvil]

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!

 

[shg]

Dynamic ranges don't appear in the names box, never have to my recollection.

 

[Aladin Akyurek]

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

 

[arkusM]

If you hit F3 they should appear.

 

[pecosvil]

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]

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]

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]

I don't know of any particular reason they don't appear, except perhaps to avoid the overhead of calculating them when selected.

 

[Fazza]

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.

'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]

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!