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.
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]].
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.
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.
CLOSE the Operating Expense Dashboard and Fleet Operating Costs workbooks.