Office X for Macintosh: The Missing Manual

536 office x for macintosh: the missing manual

formula. Excel returns you to the original document, where you’ll see the Docu-

ment B cells written out in a path notation (see the Tip below).

Once you’ve set up such a cell reference, Excel will automatically update Document

A each time you open it with Document B already open. And if Document B is

closed, Excel asks if you want to update the data. If you say yes, Excel looks into

Document B and grabs whatever data it needs. If somebody has changed Document

B since the last time Document A was opened, Excel recalculates the worksheet based

on the new numbers.

Tip: If you want Document A updated automatically whenever you open it (and don’t want to be inter-

rupted with Excel’s request to do so), choose Edit→Preferences, click Edit, and then turn off “Ask to

update automatic links.” Excel now automatically updates the link with the data from the last saved version

of Document B.

Auditing

Every now and then, you’ll find a formula whose cell references are amiss. If the

formula references another formula, tracing down the source of your problems can

be a real pain. Excel’s Auditing tools can help you access the root of formula errors

by showing you the cells that a given formula references and what formulas refer-

ence a given cell. Brightly colored tracer arrows appear between cells to indicate how

they all relate to each other.

The key to correcting formula errors is the Tools→Auditing menu item, which has

five submenu choices:

• Trace Precedents draws arrows from the currently selected cell to any cells that

provide values for its formula.

• Trace Dependents draws arrows from the currently selected cell, showing which

other formulas refer to it.

•Trace Error draws an arrow from an active cell containing a “broken” formula to

the cell or cells that caused the error.

• Remove All Arrows hides all the auditing arrows.

• Show Auditing Toolbar hides or shows the Auditing Toolbar. This toolbar’s but-

tons turn on (and off) the kinds of arrows described in the previous paragraphs,

all in an effort to help you trace how formulas and cells relate with each other.

Working with Databases

Excel has much in common with database programs. Both kinds of software keep

track of a list of records (like cards in a card catalog—or rows of a spreadsheet), and

let you browse through those records and even perform some calculations on them.

No wonder Excel is so adept at incorporating database files into its spreadsheets;

Excel X can access data in Web pages and FileMaker Pro databases, and may be able

Advanced

Formula Magic