Hi onedaywhen,

Have you considered using a defined name like ColRef=Column($C$1) ?

I believe a defined name is only calculated whenever a cell that directly
refers to it is calculated, but if you have multiple cells referring
directly to a name, and you recalculate all these multiple cells, then the
name will be recalculated multiple times. So defined names do not work
exactly like cells: its more like the name gets substituted by its refersto
whenever a formula containing the name gets calculated.

Charles Williams
www.DecisionModels.com


"onedaywhen" wrote in message
om...
I have a defined Name 'ColRef' that refers to the position of a column
of data and is defined as follows:

=3

In other words, my 'Ref' data column is presently in column C on
Sheet1 and I've hard-coded its relative position (I use this Name in
other Names to define 'dynamic ranges').

If the position of the column ever changes, say I manually insert a
column to the left and my 'Ref' column is now column D, I have to
change the 'ColRef' defined Name e.g.

=4

Because I'm lazy, I'm considering making this dynamic by getting Excel
to look for my column name in row 1:1 i.e.

=MATCH("Ref",Sheet1!$1:$1,0)

But because my primary concern is efficiency, I wondered how often the
value of this defined Name actually gets calculated.

Specifically, does it get calculated each time it is used by another
defined Name? How about when the calculation mode is changed
programmatically from manual to automatic?