Microsoft Office Tutorials and References
In Depth Information
The Secret to Understanding Names
1. Activate cell A1.
2. Choose Formulas
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:
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:
For example, if you enter this formula into cell D12, it returns the sum of the values in the ten-cell
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:
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