Hi, folks!

I'd like help coding a VBA function. Here's what I'm looking for: I want to
be able to use the function in a formula like this:

dataCol("Foo")

where "Foo" is a match from a predefined header column. I'm in Excel 2007.

The idea is to be able to move or add columns to my design dynamically and
have the named ranges keep up. I'm already doing this with good results. I
have set up various functions, formulas, and ranges in the Name Manager
(thanks so much to its authors!) such that:

o "HeadRow" finds the row number my header is on (and I can change it!)
o "rgHeadRow" is the range of "HeadRow:HeadRow"
(via the INDIRECT function) so I'll be able to use MATCH there.
o "DatColA" is my range of data in Column A

And so on.

o "DatColFoo" is set to "=OFFSET(DatColA,0,MATCH("Foo",rgHeadRow,0)-1)"

So I can use "DatColFoo" in functions such as SUMPRODUCT or whatever, and it
will be the data column whose header says "Foo". It's very handy. If I
later insert a column, "DatColFoo" still ends up correct.

But I don't want to do this with 20-30 columns, naming each one manually.
That's the point. So I want to have the function as described up-top. Then
I'd be able to use in formulas things like this:

=SUBTOTAL(DataCol("Foo"),--(DataCol("Foo")0),--(DataCol("Bar")="C"))

I'm doing that already. But I have had to predefine "DatColFoo" and
"DatColBar" and all the others, and that's what I want to let my function do
for me. I've ever only made one or two functions before, and I don't really
know how to approach this. Will someone take up the challenge?

Much obliged,
Sarah