Excel Tip of the Week #387 - Revisiting fixing broken links

Cross-workbook links and why they can go wrong

Before we get too embedded in the details, let’s look at cross-workbook links more generally and the problems these can create.

Write a formula reference between two open workbooks and you’ll see something like this:

The syntax is clear enough and easy to read, but does depend on the other workbook being open. Close it, and you’ll see this instead:

This is informative but hardly succinct – the full filepath of the source workbook is spelled out. Finally, here’s what happens if we then move the source workbook elsewhere and then reopen the file with the formula at a later date:

Notably, the cell value remains as 1 – the last known value for that source – but there’s no longer any way to know if this is accurate to whatever’s in the source workbook. This behaviour can mean that an innocent user ends up working with expired data without realising it – especially as most people are trained to dismiss error messages without reading or thinking about them.

How to fix or remove links

The first and best option when dealing with a broken link is to un-break it.  If you know where the file has moved to, then clicking Edit Links, either from the above error prompt or from the Data Ribbon, will let you see what the issue is:

Update Values will try and get the latest values from the source (even if it is closed), which can be useful, but in our case we want to Change Source. Update to the latest location and the value will be updated and the proper connection restored.

If the source workbook is lost, or we no longer want the link to exist, instead we can use Break Link to replace the cross-workbook link with a hardcoded value matching the most recently available data from the source workbook. This is quick and automatic and will apply to all formulas in the current workbook… But only formulas. This causes plenty of issues where the “Break Link” button appears not to function because the cross-workbook link appears somewhere other than a formula.

Here's a full inventory of where a cross-workbook link could be hiding and how to fix it:

Named ranges

You can include a cross-workbook reference in the definition of a named range:

While Break Links will convert any cells using this named range to values, the named range itself will persist. You can detect these by opening the Name Manager from the Formulas Ribbon, where external links are easy to see, and can also amend or delete the names from there.

Data validation rules

While you can’t create direct references to other workbooks using data validation, the INDIRECT function can be used in a custom rule to call upon an external workbook:

These can be detected by finding cells with data validation rules and then inspecting them from the Data Ribbon.

Hidden worksheets

While formulas on hidden worksheets will be updated when using Break Links, if instead you are just trying to find these links, this is a common way to miss some. You can find cross-workbook links by e.g. doing a Find on “.xls” or “[“ – but hidden worksheets will be skipped.

Conditional formatting rules

Much like data validation, you can’t create direct references to other workbooks in custom conditional formatting rules, but you can use INDIRECT:

These can be found using Home => Conditional Formatting => Manage Rules, and amended or removed from there.

Macro buttons

This is another one where the Break Links functionality will work – although it might break your source workbook based on my testing! – but finding these links can be harder. You can at least see which functions exist from Developer => Macros.

Chart elements

One final example where Break Links works, but you might struggle to find a cross-workbook link – did you know that you can set chart titles and other elements to be equal to cells?

This is another place that you can only really find through checking.

Hopefully this all gives you some insight into how cross-workbook links work, how they go wrong, and how to find and fix them!

You may also like

• Previous post - Power Pivot: RELATEDTABLE
• TOTW index