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.