Finding the missing link – Edit links enhanced

Links between workbooks

It is possible for one Excel workbook to link to other Excel workbooks in order to retrieve information from the external workbook. The obvious way to create a link is within a formula. If the workbook that you want to refer to is open, you can just select a cell in the other workbook when entering an Excel formula in the same way you would select a cell within the same workbook. The cell reference will be created with the reference to the external workbook included within square brackets:

='[Source workbook.xlsx]Data'!$A$4

Note that links to workbooks that are not currently open will display the full file path and filename rather than just the workbook name.

When both workbooks are open, the linked data should refresh automatically when the source is changed. When the source workbook is not open, there are three alternatives for refreshing the data. You can choose to be asked before Excel refreshes links; to update links automatically when the linking file is opened or not to refresh links. Links can be refreshed manually.

Edit Links

Until a recent enhancement, you could discover whether a workbook includes one or more links to another workbook, and manage any links, using the Edit Links command in the Queries and Connections group of the Data Ribbon tab. This is now being replaced by a Workbook Links command. It is still possible to access the legacy Edit Links command by customising the Quick Access Toolbar to include it. The Edit Links command opened a dialog box, whereas the new Workbook Links command opens a pane at the right of the Excel window:

With some significant exceptions, the options in the dialog and the pane are the same. Clicking on the ellipsis button at the bottom right-hand corner of each link in the Workbook Links pane displays the following options (shown with the Edit Links equivalent in brackets):

• Open workbook (Open Source)
• Copy link – this copies the link filename and path to the clipboard
• Change source (Change Source)
• Break links (Break Link)

The Edit Links ‘Update Values’ command is now available as the Refresh command on the face of the link in the Workbook Links pane, together with the status of the link. The link also now displays information about the type of links in use. In this example, we have created an Excel Range Name that refers to a cell in the external workbook. The status shows that: ‘This link exists in a name’. Clicking on the ‘name’ hyperlink will open the Name Manager dialog box but will not select the Range Name itself within the dialog. At the top of the Workbook Links pane are commands that refer to all the links in the workbook, allowing you to Refresh all, Break all or to choose your refresh settings via the settings dropdown:

Finding the missing link

Perhaps the most significant enhancement is the inclusion of the Find next command for each link. Finding links in a workbook has long been an issue and third-party add-ins have been created to help find the more obscure types of link.

As well as a link to another workbook in an Excel formula, links can be created by linking chart data or chart titles to cells in another workbook; by linking objects, such as the text in a shape, to cell contents in another workbook and, as we have seen, by including a reference to another workbook within an Excel Range Name. Various techniques were available to discover these links before the new Workbook links command was introduced, such as using Find to search for characters that would be part of any external reference such as square brackets or part of the file extension. Now, the Find next command for each link will select cells that contain formulas with external references, including formulas that include range names that themselves refer to other workbooks. In addition, it will select objects and charts that refer to other workbooks:

More details of the new Workbook links command can be found in a Microsoft Manage Workbook Links article:

Conclusion

You can explore many of the techniques covered by this article, and a great deal more, in the ICAEW archive:

• Browse the archive