Microsoft Office Tutorials and References

In Depth Information

**Doing a Multi-sheet Calculation**

answer—104. And as with any Excel formula, its result will automatically recalculate, should either of

its two contributing values—the 56 and the 48—be changed.

Now you may want to know why Sheet2! is attached to the second of the two cell references in the

formula, but nothing like it accompanies the first. That’s because the first cell reference–D12—appears

in the
same sheet as the one in which the formula
was written
, and Excel assumes, by default, that unless

the user indicates something to the contrary, all the cell references in a formula
and
the formula itself

emanate from the same sheet—and after all, isn’t that usually the case?

Now had we added these two numbers in a formula composed in
Sheet3
instead—the sheet that

contains
neither
of the values we wanted to add—we would have clicked in a cell on Sheet3, typed =,

and then clicked on the two cells in Sheet1 and Sheet2 respectively. The formula would in this case

have looked like this:

=Sheet1!D12+Sheet2!B3

See why? In this case
neither
cell shares its worksheet location with that of the formula itself, and

so Excel needs to specify the worksheets on which
both
ce l l s a r e posi ti on e d.

So that’s the general approach to multi-sheet cell references in a formula—enter the = sign and

the mathematical operation (or function) you wish to perform, and then click on the sheet and the

cell(s) on that sheet you wish to incorporate into the expression. And if you need to reference a range

of cells from another worksheet, you can just type = in your current destination cell, click on the first

cell in the range on the sheet from which you wish to copy, then drag the appropriate range length, and

press Enter. All those cells from the source worksheet are now referenced here, because they’ll
all
be

accompanied by the Sheet1, Sheet2, etc., identifier.

Now here’s a neat variation on that theme. Suppose you want to add a group of cells, all of which

ha v e the
same address
in a collection of different worksheets—for example, values in the cell A3 in

Sheets 1, 2, and 3. Let’s try it:

•

In those three cells, enter 86, 72, and 4.

•

In cell C19 on Sheet1, enter =SUM( .

Then click on cell A3 in Sheet1, hold down the Shift key, and click on the Sheet3

tab.

You should see this in the formula bar:

=SUM(‘Sheet1:Sheet3!’A3)

Press Enter, and your answer—162—flashes into the cell (note you don’t have to type the close

parentheses. Pressing Enter automatically supplies it). With this technique, Excel automatically

references the same cell in all the selected sheets—and by tapping the Shift key we’ve really grouped

all three sheets (this method works with any Excel function, not just SUM. In fact, once you press Shift

and click on the last of the sheets you want to reference, you can go ahead and drag any range you

want. That range, with precisely the same coordinates, will be selected on all the sheets, and all will be

calculated into the formula. Thus:

•

=AVERAGE(‘Sheet1:Sheet3!’A6:B14)

will calculate the average of all the values in the A6:A14 ranges on the three sheets.

Note in addition that a named range on one worksheet can be directly referenced on any other

worksheet, without any concern for relative cell reference complications (note: this assumes the

range’s scope is the Workbook, which is the default in any case. See the Appendix on range names.)

Understand a key point here-that the cell coordinates of a named range don’t change—they’re treated

as absolute references (a point elaborated in the appendix) by default. Thus you can write:

=MAX(Scores)