In addition to naming cells, ranges, and constants, you can also enter a formula directly into the Refers To box in the Define Name dialog box to create a named formula. The formula that you enter uses cell references relative to the active cell — the cell that receives the formula. If you use the mouse to indicate related cells in the act of building a formula, however, the references will be absolute.
Figure 3-4 shows a formula (=ATB1) entered directly in the Refers To box in the Define Name dialog box. In this case, the active cell is C1, so the formula refers to the two cells to its left. (Notice that the cell references are relative.) After this name is defined, entering =Power into a cell raises the value two cells to the left to the power represented by the cell directly to the left. For example, if B10 contains 3 and C10 contains 4, entering the following formula into cell D10 will return a value of 81 (3 to the 4th power):
When you call up the Define Name dialog box after creating the named formula, you'll find that the Refers To box displays a formula that is relative to the active cell. For example, if cell D32 is active, the Refers To box displays
Notice that Excel appends the worksheet name to the cells references used in your formula. This, of course, will cause the named formula to produce incorrect results if you use it on a worksheet other than the one in which it was defined. If you would like to use this named formula on a sheet other than Sheet1, you need to remove the sheet references from the formula (but keep the exclamation points). For example:
After you understand the concept, you might discover some new uses for named formulas. One distinct advantage is apparent if you need to modify the formula. You can just change the definition in the Names in Workbook field rather than edit each occurrence of the formula.
The companion CD-ROM contains a workbook with several examples of named formulas.
Was this article helpful?