Microsoft Office Tutorials and References
In Depth Information
Linking to data in other
For example, the reference =‘[FleetOperatingCosts.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 (the worksheet) 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. The single quotes around the workbook name and worksheet name are there to
account for the space in the Truck Fuel worksheet’s name. This type of reference is known
as a 3-D reference, reflecting the three dimensions (workbook, worksheet, and cell range)
that you need in order to point to a group of cells in another workbook.
TIP 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 table. Such links include the workbook name, worksheet
name, name of the Excel 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]].
IMPORTANT Hiding or displaying a table’s Totals row affects any links to a cell in that row.
Hiding the Totals row causes references to that row to display a #REF! error message.
Whenever you open a workbook that contains a link to another document, Excel tries to
update the information in linked cells. If the program can’t find the source, as would
happen if a workbook or worksheet is deleted or renamed, an alert box appears, telling you
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 data you want.
If you enter a link and you make an error, a #REF! error message appears in the cell that
contains the link. To fix the link, click the cell, delete its contents, and then either reenter the
link or create it by using the method described earlier in this section.
TIP Excel tracks workbook changes, such as when you change a workbook’s name, very
well. Unless you delete a worksheet or workbook, or move a workbook to a new folder,
odds are good that Excel can update your link references automatically to reflect the