As you may know, I use external data extensively. Did I say extensively? I meant EXTENSIVELY! For years I would make each column it’s own named range so I could use it in array formulas. If you start the range name at the row below the header row, it will adjust as the external data range adjusts (depending on your data range properties). Then one magical day I remembered a little menu item named Create, or more specifically, Insert > Name > Create. It creates range names for you. How cool is that?
Take this external data range:
Next, select the entire external data range. I like to select the name (the one that’s created when you create an external data table) from the name box. It selects the whole range. Then Insert > Name > Create.
Now I have a range for every column whose name is the column header. And all the ranges start in row 2, so they’ll adjust appropriately.
I can use those names in formulas, like
The downside is that the range names may not be exactly what you want. You’re stuck with the column headers. But if you have more than a couple of columns, the time savings is worth it.
Nice. It should have occurred to me that names expand with querytables.
I use a Name for each column too.
For non-external tables, I’ll first find the number of rows… assuming there is a non-blank column available.
Table_RowCount: =COUNTA($A:$A)
Then for each column:
Table_Column1: =OFFSET($A$1, 1, 0, Table_RowCount – 1)
I’m really looking forward to Excel 12 where RowCount becomes obsolete, but that’s another story.
Rob
Using shortcut:
1. Select the whole used range by using Ctrl+A.
2. Ctrl+Shift+F3 to show the dialog Create Names.
And yes I’m a power user when it comes to use names in this context :)
Kind regards,
Dennis
I use this a lot in conjunction with my Name Manager (http://www.jkp-ads.com/officemarketplacenm-en.htm):
– Insert, Create names
– Open Name Manager and have it create a list of names (also available through Insert, Name, Paste although Name Manager also pastes hidden names)
– Edit the refersto formulas in the worksheet, e.g. to make them dynamic. This is great, because I can use text functions to build the formulas.
– Open Name Manager and click the button to update the names with the refersto formulas that have been edited in the cells.
The main problem I have with “Names Create” is that it gives all the names global scope (instead of being worksheet level).
I much prefer sheet-level names unless I have a compelling reason to share them at the workbook level.
M
Which is what I use Name Manager for too: select all newly created names and hit the localise button.
My favorite (but not only) use for the Name Manager is to switch between local and global names. I also like the ability to filter my view and to multi-select names for whatever operation I wish to perform (like localizing names!).
I think my three favorite Excel add-ins are the Name Manager, Rob Bovey’s Chart Labeler, and Stephen Bullen’s Code Indenter (which is actually a VBE add-in).