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.

=FirstChar

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
).

=SUM(Sheet1!$A$1:$D$4)

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.

=Total

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.