Excel yourself: Fix broken links

Excel Yourself

How to make links to an external file more robust.

Download a sample spreadsheet: Data
Download a sample spreadhseet: Report

Is there a way to make links to an external file more robust? They seem to be easily broken when columns are inserted or deleted.

The solution involves range names.

While there is a commonly held view that range names add complexity to Excel files and should be avoided, I argue that when used correctly they add structure.

Structure is a key factor in creating reliable and scalable Excel files.

Range names also simplify the formula creation and maintenance process. In our case, they offer a way to handle external link issues.

You don’t think twice about naming a sheet or a file. You can also name cells and ranges.

Once created, a range name can be used in formulas to refer to that cell or range.

Figure 1

Figure 1

This has the added advantage of reducing the need to use the $ sign in cell or range references.

The problem with external references is that if the source file is modified while the destination file is closed, it can corrupt the links.

For example, let’s say we have two separate Excel files, one called Data and one called Report.

The Report file links to the Data file to extract values.

For the example there is a sheet in the Data file called Data1, where all the data is stored. In the Report file there is a sheet called Summary where the report is created.

Figure 1 shows both sheets in their files as well as a SUM function in cell B2 of the Summary sheet, adding up the totals from column C of the Data1 sheet.

Figure 2

Figure 2

The formula in cell B2 of the Summary sheet is =SUM([Data.xlsx]Data1!$C$2:$C$1000)

This is what the formula looks like that when the Data file is open, however when it is closed it will look something like: =SUM('E:\A4\CPA INTHEBLACK\[Data.xlsx]Data1'!$C$2:$C$1000)

When the source file is closed the complete path is included in the formula, causing it to expand.

The linking problem is caused when the Report file is closed and a structural change is made to the Data1 sheet in the Data file.

The SUM function is still referring to column C and is returning zero.

Figure 3

Figure 3

The reference hasn’t updated for the inserted column because the Report file was closed when the change was made.

In practice, you typically make sure both the source and destination files are open when changes are made.

Then you save both files to capture the changes.

To handle the situation when the destination file is closed and changes are made to the source file, you can use range names to cope with structural changes.

In the Data file we will name the range C2:C1000, DataAmount.

Figure 4

Figure 4

This is a large range and to select it can take time, so I will do it in two steps which will also demonstrate another advantage with using range names.

The names you use should be descriptive. Also note that you can’t use a space in a range name.

First select the range C2:C10 in the Data1 sheet. Click in the Name Box and type DataAmount and press Enter. See Figure 4

You must press Enter; you will know the range name has been created because it will be centred in the Name Box.

I always capitalise at least one letter of a range name and I tend to capitalise the first letter of each word, as this makes it easier to read.
As well, Excel will match the capitalisation when used in a formula. If you type the name in lowercase and it stays in lowercase, then you know you have misspelled it.

We can now open the Report file and create the SUM function in the Summary sheet that refers to the range C2:C10 – this will automatically insert the range name into the SUM function as per the formula below: =SUM(Data.xlsx!DataAmount)

Figure 5

Figure 5

At the moment this is not summing the complete range as we only named the range C2:C10. Bear with me, as I will demonstrate one of the advantages of using range names.

We can save and close the Report file and then make some structural changes to the Data file and see what happens.

First we will extend the DataAmount range name to C2:C1000. This is easy to do using the Name Manager.

Click the Formulas tab and click the large Name Manager icon. See Figure 5

In the Name Manager dialog, click DataAmount and add two zeroes to the reference in the Refers To box at the bottom of the dialog.

Figure 6

Figure 6

Click the tick icon on the left to accept the changes and then click the Close button. See Figure 6

This has extended the range to the one we require. We can now insert a column as before.

Opening the Report file will now correctly add up the Amount column because the named range has automatically updated for the inserted column. See Figure 7

As we saw, we can manipulate the range name reference independently of the Report file and not break the links.

Figure 7

Figure 7

In the Name Manager dialog in Figure 6, you may have noticed a Comment column (top right).

You can edit a name (using the Edit button) and add comments to describe how the name should be used.

The comment could include the fact that it is used by an external file and should not be deleted and only modified in accordance with its use in the external file.

Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services. Questions can be sent to [email protected].

This article is from the October 2014 issue of INTHEBLACK.

October 2021
October 2021

Read the October 2021 issue of INTHEBLACK magazine.

Each month we select the must-reads from the current issue of INTHEBLACK. Read more now.