Microsoft Office Tutorials and References
In Depth Information
You can display this count in a cell by using this formula:
Note, however, that entering this formula in column A of Sheet1 will result in a circular reference error — just
what you would expect.
Notice that the cell references in the preceding named formulas are absolute references. By default, all cell and
range references in named formulas use an absolute reference, with the worksheet qualifier. But, as you can see
in the next section, overriding this default behavior by using a relative cell reference can result in some very in-
teresting named formulas.
Using named formulas with relative references
As I noted previously, when you use the New Name dialog box to create a named formula that refers to cells or
ranges, the Refers To field always uses absolute cell references and the references include the sheet name quali-
fier. In this section, I describe how to use relative cell and range references in named formulas.
Using a relative cell reference
Begin by following these steps to create a named formula that uses a relative reference:
1. Start with an empty worksheet.
2. Select cell A1.
This step is very important.
3. Choose Formulas ⇒ Defined Names ⇒ Define Name.
This brings up the New Name dialog box.
4. Type CellToRight in the Name field.
5. Delete the contents of the Refers To field and type the following formula (don't point to the cell in the
6. Click OK to close the New Name dialog box.
7. Type something (anything) into cell B1.
8. Enter this formula into cell A1:
You'll find that the formula in A1 simply returns the contents of cell B1.
Next, copy the formula in cell A1 down a few rows. Then enter some values in column B. You'll find that the
formula in column A returns the contents of the cell to the right. In other words, the named formula
( CellToRight ) acts in a relative manner.
You can use the CellToRight name in any cell (not just cells in column A). For example, if you enter
=CellToRight into cell D12, it returns the contents of cell E12.