Link preservation

Q: I have created a “digital dashboard” in an Excel workbook that summarizes information from multiple Excel workbooks. As long as I maintain the linked files on my PC, the links work perfectly. However, when I email the dashboard workbook and related linked workbooks to others, these links stop working for some but not for others. What are we doing wrong?

A: To preserve links between workbooks, the resulting link paths and file names must be the same on the destination computer as on the origination computer; otherwise, Excel does not know where to look for the related linked files. Presented below are three suggestions:

1. Use identical file paths. Your links should continue to work correctly as long as your recipient saves the linked files using the same path and file name used when creating the links. For example, if the linked files were originally saved to C:/Data Folder, then your recipients should also save them to C:/Data Folder on their computers.

2. Repair broken links. Excel provides tools for repairing broken links. To use these tools, your recipients should open the dashboard workbook, and from the Data tab, select Edit Links to display the Edit Links dialog box, pictured below. (As each link is selected, the expected location of each linked workbook is displayed below the Select box. Clicking the Check Status button tests the link’s validity, and if the link is broken, an error message is reported instead of the Location.)

To repair the link, select it and click the Change Source button, then navigate to the correct source file, and click OK, Close. Repeat these steps for each broken link, then resave the dashboard workbook.

3. Share files in the cloud. Another approach to preserving links is to save files in the cloud via services such as Dropbox, Google Drive, iCloud, SharePoint Server, SkyDrive, etc. All parties can then access both the dashboard workbook (which contains the links) and the linked source files, without having to manage file locations or repair links. This approach offers the added advantage that the authors of the supporting source workbooks can update their information in the cloud and the resulting dashboard workbook can reflect their changes instantly (as links are refreshed), without having to constantly resend the linked source files to the recipients.

(Keep reading as the next topic describes new link management tools available in Excel 2013, which you may find helpful.)