Microsoft Office Tutorials and References

In Depth Information

**The Secret to Understanding Names**

1.
Activate cell A1.

2.
Choose Formulas

➜

Defined Names

➜

Define Name to bring up the New Name dialog box.

3.
Type
Sum10Cells
in the Name field.

4.
Type this formula in the Refers To field:

=SUM(!B1:!K1)

After creating this named formula, you can insert the following formula into any cell in any sheet,

and it then displays the sum of the ten cells directly to the right:

=Sum10Cells

For example, if you enter this formula into cell D12, it returns the sum of the values in the ten-cell

range E12:N12.

Note that because cell A1 was the active cell when you defined the named formula, the relative

references used in the formula definition are relative to cell A1. Also note that the sheet name

was not used in the formula. Omitting the sheet name (but including the exclamation point)

causes the named formula to work in any sheet.

If you select cell D12 and then bring up the Name Manager dialog box, you’ll see that the Refers

To field for the
Sum10Cells
name displays the following:

=SUM(!E12:!N12)

The
Sum10Cells
named formula does not work if you use it in a cell that resides in a

column beyond column XET. That’s because the formula becomes invalid as it tries to

reference a nonexistent cell beyond column XFD.

Using a mixed range reference

As I discuss in Chapter 2, a cell reference can be absolute, relative, or mixed. A mixed cell

reference consists of either of the following:

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

h
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