Microsoft Office Tutorials and References
In Depth Information
The Secret to Understanding Names
After creating this named formula, you can enter the following formula into a cell. The formula
always returns the first character of cell A1 on Sheet1.
The next example uses a range reference in a named formula. Figure 3-20 shows the New Name
dialog box when defining the following named formula (named Total ).
Figure 3-20: Defining a named formula that uses a range reference.
After creating this named formula, you can enter the following formula into any cell on any sheet.
The formula returns the sum of the values in A1:D4 on Sheet1.
Notice that the cell references in the two preceding named formulas are absolute references. By
default, all cell and range references in named formulas use an absolute reference, with the
worksheet qualifier. But, as you can see in the next section, overriding this default behavior by using a
relative cell reference can result in some very interesting named formulas.
Using named formulas with relative references
As I noted previously, when you use the New Name dialog box to create a named formula that
refers to cells or ranges, the Refers To field always uses absolute cell references and the
references include the sheet name qualifier. In this section, I describe how to use relative cell and
range references in named formulas.