Microsoft Office Tutorials and References
In Depth Information
To demonstrate that the formula named CellToRight truly uses a relative cell reference, activate any cell other
than cell A1 and display the Name Manager dialog box (see Figure 3-21). You'll see that the Refers To field
contains a formula that points one cell to the right of the active cell, not A1. For example, if cell B7 is selected
when the Name Manager is displayed, the formula for CellToRight appears as follows:
Figure 3-21: The CellToRight named formula varies, depending on the active cell.
If you use the CellToRight name on a different worksheet, you'll find that it continues to reference the cell to the
right — but it's the cell with the same address on Sheet1. This happens because the named formula includes a
sheet reference. To modify the named formula so it works on any sheet, follow these steps:
1. Activate cell A1 on Sheet1.
2. Choose Formulas ⇒ Defined Names ⇒ Name Manager to bring up the Name Manager dialog box.
3. In the Name Manager dialog box, select the CellToRight item in the list box.
4. In the Refers To field, delete the sheet name (but keep the exclamation point). The formula should look like
5. Click Close to close the Name Manager dialog box.
After making this change, you'll find that the CellToRight named formula works correctly on any worksheet in