Microsoft Office Tutorials and References

In Depth Information

**The Watch Window—Spying On Your Own Data**

located, as well as the
worksheet
, too. The name in brackets—[link.xslx]—obviously points to the

workbook. Note as well that it’s the cell that
isn’t
in the same workbook as the formula that needs all

these specifications, and note also that it’s written with dollar signs, signifying an absolute reference.

T ha t’ s be ca use i f y ou copy the for mul a down a col umn , for e xa mpl e , Exce l a ssume s y ou wa n t a l l the

copied formulas to reference the same cell in that other workbook.

Keep in mind that if you currently have
only
the workbook open that contains the
linked
cell and

you change its data—and then
later
open the workbook containing the formula—the formula
will

recalculate automatically. And by the same token, let’s say you change the data in the linked cell, close

it and save it, thus leaving neither workbook open. When you open the formula-bearing workbook, it

will likewise recalculate. (Note: If you
move
one of the workbooks to a different folder, the current link

will be severed and will have to be reinstituted, if that’s what you want, requiring a fairly messy repair

job. An
Edit Link
dialog box appears, asking you to supply the new location of the moved workbook.) In

sum, wor ki n g wi th ce l l r e fe r e n ce s can be a bi t di ce y , an d shoul d be use d fr ug al l y . Apar t fr om the

moving-folder issue, tracking the cell references that contribute to your results can be daunting,

particularly if you need to analyze a mistake in a formula.

The Watch Window—Spying On Your Own Data

I’ve said it before, and I’ll say it again: workbooks are vast. You may have formulas scattered all across

its worksheets, or even in far flung cells on the same sheet. And what if you’ve written a formula

referencing cells in very different places on the workbook, such that when you changed the data in one

such cell you could no longer see the new formula result on screen, because you’ve scrolled too far

away? Well, you can always keep that result in your sights with the
Watch Window
option, located in

the
Formula Auditing
button group in the
Formulas
tab. Let’s try a very simple illustration, which

should prove its point. Just watch.

Type 71 in cell D18 on Sheet1. Then type 21 in cell A2 in Sheet2. Return to Sheet1, and write the

following formula in E17:

=D18+Sheet2!A2

Answer: 92. OK—been there, done that, got the t-shirt. But now click back onto Sheet2, and click

For mul as
Watch Window. You’ll see (Figure 7–13):

Figure 7–13.
Opaque window—watching designated cells