When is a defined Name actually calculated?
 

Excel optimizes its calculation engine to provide the fastest response which
equals minimal calculation. I have no special knowledge of how the
calculation engine works or how Excel handles the calculation of names. I
recall there is a KB article that talks a bit about how Excel recalculates
(see link below) and Charles has done a lot of empirical research and formed
opinions on how it operates, so perhaps you can get a better answer from
him. Unless you are having some type of problem, I wouldn't waste a lot of
hand wringing over it. Did you have an alternative in mind?

Anyway, here is an article that provides as much information as I am aware
of on how Excel recalculates:

http://msdn.microsoft.com/library/de...c_xlrecalc.asp

--
Regards,
Tom Ogilvy



"onedaywhen" wrote in message
om...
Tom, Charles,

Thanks you your collective suggestion of the COLUMN formula, one which
I hadn't considered.

The thing is, I also want to anticipate the situation where the value
'Ref' in C1 is cut and pasted to another cell in row 1:1. The COLUMN
formula wouldn't work but the MATCH one would. Apologies for not
making this clear (I only just consciously considered it myself,
however!)

So I'm happy with my Name's formula but my question was about when it
gets calculated.

Tom, I'm sorry, I'm not sure I fully understand your reply. Just to
clarify, my concern isn't that it won't be calculated, rather I'm
concerned it's being calculated too often.

I *think* my MATCH formula is calculated only when something in row
1:1 is changed, including the insertion of a column. Perhaps it is
calculated when the workbook is first open. Both these cases are OK,
they don't happen very often.

But I wondered whether the formula gets calculated every time the Name
is used in another Name. Here's some more of my Names:

Data:
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,COUNTA(Sheet1!$1:$1))

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

Refs
=OFFSET(Data,0,ColRef-1,,1)

So, each time I use the 'Refs' Name does Excel recalculate the
'ColRef' Name? Or does Excel store the value of 'ColRef'' and only
recalculate the formula when a change is made that affects row 1:1?

Thanks again.

"Tom Ogilvy" wrote in message
...
Define your formula to be

=COLUMN(Sheet1!$C$1)

It should be updated whenever it is appropriate

Your suggested use of match should work as well.

Again, since you supply the dependency information in the function
arguments, I would trust Excel to update it appropriately.

--
Regards,
Tom Ogilvy

When is a defined Name actually calculated?
 

As I said befo 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.

So in general its is not efficient to put calculation-intensive functions
inside names: use a spare cell somewhere instead, and if you want to use
names then give the spare cell a name.

Charles Williams
www.DecisionModels.com

"Tom Ogilvy" wrote in message
...
Excel optimizes its calculation engine to provide the fastest response
which
equals minimal calculation. I have no special knowledge of how the
calculation engine works or how Excel handles the calculation of names. I
recall there is a KB article that talks a bit about how Excel recalculates
(see link below) and Charles has done a lot of empirical research and
formed
opinions on how it operates, so perhaps you can get a better answer from
him. Unless you are having some type of problem, I wouldn't waste a lot
of
hand wringing over it. Did you have an alternative in mind?

Anyway, here is an article that provides as much information as I am aware
of on how Excel recalculates:


http://msdn.microsoft.com/library/de...us/dnexcl2k2/h
tml/odc_xlrecalc.asp

--
Regards,
Tom Ogilvy



"onedaywhen" wrote in message
om...
Tom, Charles,

Thanks you your collective suggestion of the COLUMN formula, one which
I hadn't considered.

The thing is, I also want to anticipate the situation where the value
'Ref' in C1 is cut and pasted to another cell in row 1:1. The COLUMN
formula wouldn't work but the MATCH one would. Apologies for not
making this clear (I only just consciously considered it myself,
however!)

So I'm happy with my Name's formula but my question was about when it
gets calculated.

Tom, I'm sorry, I'm not sure I fully understand your reply. Just to
clarify, my concern isn't that it won't be calculated, rather I'm
concerned it's being calculated too often.

I *think* my MATCH formula is calculated only when something in row
1:1 is changed, including the insertion of a column. Perhaps it is
calculated when the workbook is first open. Both these cases are OK,
they don't happen very often.

But I wondered whether the formula gets calculated every time the Name
is used in another Name. Here's some more of my Names:

Data:
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,COUNTA(Sheet1!$1:$1))

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

Refs
=OFFSET(Data,0,ColRef-1,,1)

So, each time I use the 'Refs' Name does Excel recalculate the
'ColRef' Name? Or does Excel store the value of 'ColRef'' and only
recalculate the formula when a change is made that affects row 1:1?

Thanks again.

"Tom Ogilvy" wrote in message
...
Define your formula to be

=COLUMN(Sheet1!$C$1)

It should be updated whenever it is appropriate

Your suggested use of match should work as well.

Again, since you supply the dependency information in the function
arguments, I would trust Excel to update it appropriately.

--
Regards,
Tom Ogilvy