PDA

View Full Version : Defining a Name for a Formula



Cyberdude
10-02-2010, 12:36 PM
I don't know how this escaped my attention before, but I just discovered that when I define a name for a formula, the system always prefaces a reference to a cell with the sheet name. To oversimplify, if I want to give a name to the formula:
= 2 * $A$1
then the system writes the formula as:
=2 * 'Sheet 1'!$A$1
which makes the defined name largely useless to me since I want to refer
to it on several different sheets. Is there a way around this? I thought the scope "workbook" would prevent this nuisance, but it doesn't.
Sid

Paul_Hossler
10-02-2010, 03:59 PM
Not perfect, but in your specific example, INDIRECT() seems to work on different sheets


=2*INDIRECT("A1")


It's like that in 2010 also, but I believe that in 2003 it worked correctly (or at least differently )

Paul

Bob Phillips
10-03-2010, 04:13 AM
Try using the name reference as

=2*!$A$1

Paul_Hossler
10-03-2010, 07:09 AM
I'll bet that's NOT documented anywhere :clap:

Paul

Cyberdude
10-03-2010, 11:45 AM
Hey, XLD, I'd never have thought of it, but your suggestion works great and will wind up allowing me to shorten a lot of my formulas!
Soln: 2 * !$A$1
Actually what I'll be defining will usually look like
=Indirect("Quote"&!$A$1)
Who would have guessed! Thanx.
Sid :friends:

Jan Karel Pieterse
10-04-2010, 02:11 AM
Warning: Don't use that trick.

In at least a couple of Excel versions, if a calculation is triggered by ANY VBA action, all formulas using such a defined name will derive their precedents from the ACTIVE worksheet as opposed to the worksheet they are on. This may cause your model to give bogus results.

Jan Karel Pieterse
10-04-2010, 02:17 AM
I just checked on Excel 2010 and the issue has not been fixed.