Excel 2010 Bible

Part IV: Using Advanced Excel Features

590

workbook. You can create a summary workbook that first uses link formulas to retrieve specific

data from each manager’s workbook and then calculates totals across all regions.

Linking also is useful as a way to break up a large workbook into smaller files. You can create

smaller workbooks that are linked with a few key external references.

Linking has its downside, however. External reference formulas are somewhat fragile, and acciden-

tally severing the links that you create is relatively easy. You can prevent this mistake if you under-

stand how linking works. Later in the chapter, I discuss some problems that may arise as well as

how to avoid them. (See “Avoiding Potential Problems with External Reference Formulas.”)

On the CD
The companion CD-ROM contains two linked files that you can use to get a feel for how linking works. The
files are named source.xlsx and dependent.xlsx. As long as these files remain in the same folder, the
links will be maintained. n

Creating External Reference Formulas

You can create an external reference formula by using several different techniques:

l Type the cell references manually. These references may be lengthy because they

include workbook and sheet names (and, possibly, even drive and path information). The

advantage of manually typing the cell references is that the source workbook doesn’t have

to be open. The disadvantage is that it’s very error prone. Mistyping a single character

makes the formula return an error (or possibly return a wrong value from the file).

l (^) Point to the cell references. If the source workbook is open, you can use the standard
pointing techniques to create formulas that use external references.
l (^) Paste the links. Copy your data to the Clipboard. Then, with the source workbook open,
choose Home ➪ Clipboard ➪ Paste ➪ Paste Link (N). Excel pastes the copied data as
external reference formulas.
l Choose Data ➪ Data Tools ➪ Consolidate. For more on this method, see the section
“Consolidating worksheets by using the Consolidate command,” later in this chapter.

Understanding link formula syntax

The general syntax for an external reference formula is as follows:

=[WorkbookName]SheetName!CellAddress

Precede the cell address with the workbook name (in brackets), followed by the worksheet name

and an exclamation point. Here’s an example of a formula that uses cell A1 in the Sheet1 work-

sheet of a workbook named Budget:

=[Budget.xlsx]Sheet1!A1