Microsoft Office Tutorials and References
In Depth Information
The Secret to Understanding Names
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
Name Manager to bring up the Name Manager
3. In the Name Manager dialog box, select the CellToRight item in the list box.
4. Delete the contents of the Refers To field and type this formula:
5. Click OK 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 the workbook.
The named formula does not work if you use it in a formula in column XFD because the
formula attempts to reference a nonexistent cell. (There is no column to the right of
Using a relative range reference
This example expands upon the previous example and demonstrates how to create a named
formula that sums the values in ten cells directly to the right of a particular cell. To create this
named formula, follow these steps: