In , Roger Govier
spake thusly:

Thanks, Roger. I actually also did think about using MATCH with
the column names to automate the named-range formulas further,
similarly to what you have done. I didn't implement it because
(a) I would have needed an hour or so of trial and error to get
it to work (and anyway, I just started all this today); and (b)
it still requires a bunch of similar named ranges rather than
one that could self-populate based on the argument. Hmm.
Nevertheless, I find your method pretty slick, and also feel
somewhat vindicated to find that I chose an approach that
turns out to be similar to yours.

Obtw: what's the advantage to having "List" with all the
column headers rather than matching on Row 1 itself?

Dallman

===============
Hi Dallman

Not sure whether this gets you any further, but I use a broadly similar
technique for defining ranges.

I have a Name Arng = Sheet1!$A$1:$Z$10000 or some suitably large size
to cope with the maximum range of data I am going to be dealing with.
I have lr (for Lastrow) using COUNTA() as you do to determine the last
used row of the sheet.
I have a List set up, which is a list of all of my column headings from
the sheet

Then for each named range, it is the same formula, just changing the
column Name e.g.
Analysis =
INDEX(ARng,1,MATCH("Analysis",List,0)):INDEX(Arng, lr,MATCH("Analysis",List,0))
Inv Date = INDEX(ARng,1,MATCH("Inv
Date",List,0)):INDEX(Arng,lr,MATCH("Inv Date",List,0))

It becomes a simple quick Copy and Paste of Defined Name into the one
formula to create each range.