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
Search JabSto ::




Custom Search