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.
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.
Search JabSto ::

Custom Search