Microsoft Office Tutorials and References

In Depth Information

• An absolute column reference and a relative row reference (for example, $A1)

• A relative column reference and an absolute row reference (for example, A$1)

As you might expect, a named formula can use mixed cell references. To demonstrate, activate cell B1. Use the

New Name dialog box to create a formula named
FirstInRow,
using this formula definition:

=!$A1

This formula uses an absolute column reference and a relative row reference. Therefore, it always returns a

value in column A. The row depends on the row in which you use the formula. For example, if you enter the

following formula into cell F12, it displays the contents of cell A12:

=FirstInRow

And, of course, you can create in
FirstInColumn
named formula. Activate cell A2 and create a
FirstInColumn

name using this formula:

=!A$1

You can't use the FirstInRow formula in column A, and you can't use the FirstInColumn

formula in row 1. In either case, it generates a circular reference — a formula that refers

to itself.

Advanced Techniques That Use Names

This section presents several examples of advanced techniques that use names. The examples assume that

you're familiar with the naming techniques described earlier in this chapter.

Using the INDIRECT function with a named range

Excel's INDIRECT function lets you specify a cell address indirectly. For example, if cell A1 contains the text

C45, this formula returns the
contents
of cell C45:

=INDIRECT(A1)

Figure 3-23 shows a worksheet with 12 range names that correspond to the month names. For example,
January

refers to the range B2:E2. Cell B16 contains the following formula:

=SUM(INDIRECT(A16))

This formula returns the sum of the named range entered as text in cell A16.