Microsoft Office Tutorials and References

In Depth Information

Interestingly, the CellToRight named formula works even if you use it in column XFD

(the last column, which has no column to its right). The formula displays the value in

column A. In other words, it's as if the worksheet wraps around, and column A comes

after column XFD.

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 12 cells directly above a particular cell. To create this named formula, follow these steps:

1.
Activate cell A13 (very important).

2.
Choose Formulas
⇒
Defined Names
⇒
Define Name to bring up the New Name dialog box.

3.
Type
Sum12Cells
in the Name field.

4.
Type this formula in the Refers To field:

=SUM(!A1:!A12)

After creating this named formula, you can insert the following formula into any cell (as long as it's in row 13

or a higher number row) in any sheet, and it then displays the sum of the 12 cells directly to the above:

=Sum12Cells

For example, if you enter this formula into cell D40, it returns the sum of the values in the 12-cell range

D28:D39.

Figure 3-22 shows an example of this named formula in use.