Linking to Data in Other Worksheets and Workbooks

Cutting and pasting data from one workbook to another is a quick and easy way to gather related data in one place, but there is a substantial limitation: if the data from the original cell changes, the change is not reflected in the cell to which the data was copied. In other words, cutting and pasting a cell's contents doesn't create a relationship between the original cell and the target cell.

You can ensure that the data in the target cell reflects any changes in the original cell by creating a link between the two cells. Instead of entering a value into the target cell by typing or pasting, you create a type of formula that identifies the source from which Excel 2007 will derive the target cell's value.

To create a link between cells, open both the workbook with the cell from which you want to pull the value and the workbook with the target cell. Then click the target cell and type an equal sign, signifying that you want to create a formula. After you type the equal sign, activate the workbook with the cell from which you want to derive the value and then click that cell.

When you switch back to the workbook with the target cell, you see that Excel 2007 has filled in the formula with a reference to the cell you clicked.

The reference from the example ='[Fleet Operating Costs.xlsx]Truck Fuel'!$C$15 gives three pieces of information: the workbook, the worksheet, and the cell you clicked in the worksheet. The first element of the reference, the name of the workbook, is enclosed in square brackets; the end of the second element is marked with an exclamation point; and the third element, the cell reference, has a dollar sign before both the row and the column identifier. This type of reference is known as a 3-D reference, reflecting the three dimensions (workbook, worksheet, and cell) that you need to point to a cell in another workbook.

Note

For references to cells in the same workbook, the workbook information is omitted. Likewise, references to cells in the same worksheet don't use a worksheet identifier.

You can also link to cells in an Excel 2007 data table. Such links include the workbook name, worksheet name, name of the table, and row and column references of the cell to which you've linked. Creating a link to the Cost column's cell in a table's Totals row, for example, results in a reference such as ='FleetOperatingCosts.xlsx'!Truck Maintenance[[#Totals],[Cost]].

Caution

Hiding or displaying a table's Totals row affects a link to a cell in that row. Hiding a table Totals row causes any references to that row to display a #REF! error message.

Whenever you open a workbook with a link to another document, Excel 2007 tries to update the information in linked cells. If the program can't find the source, such as when a workbook or worksheet is deleted or renamed, an alert box appears to indicate that there is a broken link. At that point, you can click the Update button and then the Edit Links button to open the Edit Links dialog box and find which link is broken. After you identify the broken link, you can close the Edit Links dialog box, click the cell containing the broken link, and create a new link to the desired data.

If you type a link yourself and you make an error, a #REF! error message appears in the cell with the link. To fix the link, click the cell, delete its contents, and then either retype the link or create it with the point-and-click method described earlier in this section.

Note

Excel 2007 is much better at tracking workbook changes, such as new worksheet names, than earlier versions of the program. Unless you delete a worksheet or workbook, or move a workbook to a new folder, odds are good that Excel 2007 can update your link references to reflect the change.

In this exercise, you'll create a link to another workbook, make the link's reference invalid, use the Edit Links dialog box to break the link, and then re-create the link correctly.

USE the Operating Expense Dashboard and Fleet Operating Costs workbooks from the My Documents\Microsoft Press\Excel SBS\MultipleFiles folder.

OPEN the Operating Expense Dashboard and Fleet Operating Costs workbooks.

1.	In the Operating Expense Dashboard workbook, in cell I6, type =, but do not press .
2.	On the View tab, in the Window group, click Switch Windows and then click Fleet Operating Costs. The Fleet Operating Costs workbook appears.
3.	If necessary, click the Plane Repairs sheet tab to display the Plane Repairs worksheet and then click cell C15. Excel 2007 sets the cell's formula to ='[Fleet Operating Costs.xlsx]Plane Repairs'!$C$15.
4.	Press . Excel 2007 displays the Operating Expense Dashboard workbook; the value $2,410,871.00 appears in cell I6. Note Yes, cell C15 on the Plane Repairs worksheet contains the wrong total for the Airplane Fuel category; that's why you replace it later in this exercise.
5.	On the View tab, in the Window group, click Switch Windows and then click Fleet Operating Costs. The Fleet Operating Costs workbook appears.
6.	Right-click the Plane Repairs sheet tab and then click Delete. In the message box that appears, click Delete to confirm that you want to delete the worksheet. Excel 2007 deletes the Plane Repairs worksheet.
7.	On the View tab, in the Window group, click Switch Windows and then click Operating Expense Dashboard. The Operating Expense Dashboard workbook appears.
8.	On the Data tab, in the Connections group, click Edit Links. The Edit Links dialog box appears.
9.	Click Break Link. Excel 2007 displays a warning box asking if you're sure you want to break the link.
10.	Click Break Links. The warning box disappears, and Excel 2007 removes the link from the workbook.
11.	Click Close. The Edit Links dialog box disappears and the error code #REF! appears in cell I6.
12.	In cell I6, type =, but do not press Enter.
13.	On the View tab, in the Window group, click Switch Windows and then click Fleet Operating Costs. The Fleet Operating Costs workbook appears.
14.	Click the Plane Fuel sheet tab. The Plane Fuel worksheet appears.
15.	Click cell C15 and then press . Excel 2007 displays the Operating Expense Dashboard workbook with the value $52,466,303.00 in cell I6.

CLOSE the Operating Expense Dashboard and Fleet Operating Costs workbooks.