Microsoft Office Tutorials and References

In Depth Information

**Getting explicit about intersections**

Using Go To with names

When you click the Find & Select button on the Home tab and click Go To (or press F5), any

names you defined appear in the Go To list, as shown in Figure 12-20. Select a name, and

click OK to jump to the range to which the name refers. Note that names defined with

constants or formulas do not appear in the Go To dialog box.

Figure 12-20
Use the Go To dialog box to quickly select a cell or range by name.

Getting explicit about intersections

In the worksheet in Figure 12-19, if you type the formula
=Qtr_1*4
in cell I4, Excel assumes

you want to use only one value in the Qtr_1 range B4:B7—the one in the same row as the

formula that contains the reference. This is called
implicit intersection
. Because the formula

is in row 4, Excel uses the value in cell B4. If you type the same formula in cells I5, I6, and

I7, each cell in that range contains the formula =Qtr_1*4, but at I5 the formula refers to cell

B5, at I6 it refers to cell B6, and so on.

Explicit intersection
refers to a specific cell with the help of the intersection operator. The

intersection operator
is the space character that appears when you press the Spacebar. If

you type the formula
=Qtr_1 Region_1
at any location on the same worksheet, the space

between the names tells Excel that you want to refer to the value at the intersection of the

range labeled Qtr 1 and the range labeled Region 1, which is cell B4.

Creating three-dimensional formulas

You can use references to perform calculations on cells that span a range of worksheets

in a workbook. These are called
3-D references
. Suppose you set up 12 worksheets in

the same workbook—one for each month—with a year-to-date summary sheet on top.

If all the monthly worksheets are laid out identically, you could use 3-D reference

formulas to summarize the monthly data on the summary sheet. For example, the formula