When I create an external data table in Excel, I almost always want to have a named range that refers to every column. This way I can create array formulas and refer to column names rather than cell references. Which formula is easier to read?:
Another advantage is that the named ranges expand and contract when the external data changes. No need for dynamic ranges, because they’re already built in.
To create these named ranges, I select the entire QueryTable and choose Insert > Name > Create and choose the Top checkbox. This creates a global range name based on whatever is in the first row of the selected range.
The downside is that this creates global names and I want local names. The reason I want local names, other than simply good practice, is that I may have the same name on a different sheet. In one workbook, I have a sheet with an external data query of raw materials and another sheet for finished goods. They both have a column called QuantityOnHand.
In order to change the names to worksheet-level, I wrote this macro:
Dim nm As Name
Dim wsh As Worksheet
Dim sName As String, sRefersto As String
Set wsh = ThisWorkbook.Worksheets(“RM”)
For Each nm In ThisWorkbook.Names
If nm.Parent.Name = ThisWorkbook.Name Then
If nm.RefersToRange.Cells(0).Value = nm.Name And _
nm.RefersToRange.Parent.Name = wsh.Name Then
sName = nm.Name
sRefersto = nm.RefersTo
nm.Delete
wsh.Names.Add wsh.Name & “!” & sName, sRefersto
End If
End If
Next nm
End Sub
It looks for names whose Parent is the workbook (global names) and that are on the sheet in question. Of those, it looks for names whose Name property is equal to the cell above (cells(0)), which indicates that the name was created with Insert > Names > Create. Once the name has been identified, it saves the Name and Refersto properties so that the name can be recreated. The name is deleted and then recreated with the sheet name prefixed, thereby making it local or worksheet-level.
I tried simply changing the Name property, but nothing happened – the names stayed globally scoped. I tried deleting the name after I created a worksheet-level name, and nothing happened. I had to first delete the global name, then create the local one. By the time I was done, I realized that I should have just written my own Create routine instead of fixing already created names.
I think a ‘Create Local Names’ utility should be added to the Name Manager. What do you think?
Dick, I agree, it would be great to be able to create local names in Name Manager. Otherwise, it’s often a 2-step process – create the name in Excel and then open Name Manager to convert it to local, which I sometimes forget to do until my code doesn’t work and I realize it’s still global.
Of course, only if Jan Karel wants to. I mean, he may have other things to do :).
Doug –
You can create the name, in local or global scope, right in the Name Manager.
Woops. So that’s what that big green plus sign with the tooltip saying “create a defined name” is for. Thanks for helping me see what was in front of my face, Jon. No more ctl-F3 for me!
I didn’t realize that I could convert names to local – I thought the globe with the X on it meant the tool was unavailable. (When I hover over tools, the tooltips appear on my primary monitor, so I don’t see them.) Converting those via NM would have been faster than writing that code.
Dick, My reading comprehension is not what I would wish, or I would have just told you. I read your whole post thinking “Name Manager works like that” then got to the end and thought you were asking for a different feature which I’ve I also wanted, which, it turns out is right there anyways.
If it weren’t for this fine blog, I’d still be in the dark, so thanks once again for this great place to learn.
Dick has a good point though: we don’t currently have a ‘Create Names’ command in Name Manager, only a ‘Define Name’ command.
This is probably because neither me or Jan Karel ever uses ‘Create Name’!
If we were to add Create Name, what additional function beyond the current Excel stuff would people like?
I guess I’ve never used Excel’s Create Name either (hence my onion-like layers of ignorance in this post). Maybe now I understand Dick’s original request.
I’d like to be able to name ranges based on cell contents using patterns, e.g., if the selected cells contain “First Name”, etc. I could choose a pattern of:
“rng” & replace(cell.text,” “,””)
so the names would be “rngFirstName”, etc.
I was thinking about dynamic named ranges, but I don’t usually use that many per project, and they are often finicky, e.g., “don’t count the contents in the hidden template rows above the start of the dynamic range,” and they are often based on the contents of helper columns, so it seems like the automation would have to be very complex to create just a few ranges.
Charles: The only additional thing I want is to be able to make them locally scoped.
I always use Ctrl Shift F3 for almost everything and I always try to leave the names where possible on sheet in italics CamelCaseAndNoSpaces. I always used OAK to manage Names. Mainly because it was originally co-written by my old boss Doc Sarmecanic.
Well, I didn’t add create names, because that is one of the few things the Excel names UI does quite well.
I’d like to point you all at the “List” and “Pick up” functions though, those two are quite powerful.
I sometimes use them to build complex refersto formulas using string functions:
– in the first column you write the names
– in the second you build the refersto formulas (as strings), using any and all string parsing formulas excel has to offer (and Search and replace and whatever other text parsing you need).
– then you fire up NM and click the pickup button to create those names.
Dick: if you have a hard time remembering the icons, try the non-icon buttons (look at the bottom of the settings listbox, there are more than just three options to set there!!!).
Hi Jan,
That comments area of Name Manager in Excel 2007 is quite handy. Any plans to add that in Name Manager ?
Kanwaljit
Hi Kanwaljit,
No, not yet. As many self employed Excel devs I know, I’m swamped with work nowadays.
Good point though!
Create Names Local would be a very useful addition to Excel itself
Even just a little tick-box
Even better an option to make local names the default.