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




Custom Search