Naming formulas

In addition to naming cells, ranges, and constants, you can also create named formulas. To do so, enter a formula directly into the Refers To field in the New Name dialog box.

Note This is a very important point: The formula that you enter uses cell references relative to the active cell at the time that you create the named formula.

Figure 3-5 shows a formula (=A1° B1) entered directly in the Refers To box in the New 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 returns a value of 81 (3 to the 4th power):

=Power

New Name

Spine:

Po^tr

fupc:

Warkticali

v

CfiirfTienn

gpiVita;

1

(Ml

| CK }

Caned

Figure 3-5: You can name a formula that doesn't appear in any worksheet cell.

When you display the New Name dialog box after creating the named formula, the Refers To box displays a formula that is relative to the current active cell. For example, if cell D32 is the active cell, the Refers To box displays

=Sheeti!B32ASheet1!C32

Notice that Excel appends the worksheet name to the cell 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 formula one time rather than edit each occurrence of the formula.

CD- The companion CD-ROM contains a workbook with several examples of named formulas.

ROM The workbook is called'■■*'■ named formulas, xlsx.

Tip When you're working in the New Name dialog box, the Refers To field is normally in "point mode," which makes it easy to enter a range reference by clicking in the worksheet. Press F2 to toggle between point mode and normal editing mode, which allows you to use the arrow keys to edit the formula.

The Secret to Understanding Cell and Range Names

Excel users often refer to named ranges and named cells. In fact, I use these terms frequently throughout this chapter. Actually, this terminology is not quite accurate.

Here's the secret to understanding names:

When you create a name for a cell or a range in Excel, you're actually creating a named formula - a formula that doesn't exist in a cell. Rather, these named formulas exist in Excel's memory.

When you work with the New Name dialog box, the Refers To field contains the formula, and the Name field contains the formula's name. You'll find that the contents of the Refers To field always begin with an equal sign - which makes it a formula.

This is not exactly an earthshaking revelation, but keeping this "secret" in mind could help you understand what's going on behind the scenes when you create and use names in your workbooks.

0 0

Post a comment