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?:
=SUM((LEFT(Assembly!Name,3)="AXI")*(Assembly!QuantityOnHand))
=SUM((LEFT(Assembly!B2:B174,3)="AXI")*(Assembly!G2:G174))
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:
Sub LocalizeNames()
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?
