Microsoft Office Tutorials and References
In Depth Information
Figure 3-22: Using the Sum12Cells named formula to sum monthly values.
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 D40 and then bring up the Name Manager dialog box, you'll see that the Refers To field for the
Sum12Cells name displays the following:
If you use Sum12Cells named formula in rows 1:12, it causes a circular reference error
because of the “wrap-around” effect I noted earlier. For example, if you enter the named
formula in cell D3, it's attempting to sum the values in the range D1048567:D2, and that
range includes cell D3.
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: