[Fixed!] Excel Links Not Working Unless Source Workbook Is Open

To demonstrate the solutions to why Excel links may not be working unless the source workbook is open, we’ll use two workbooks named Workbook1 and Workbook2.

Solution 1 – Don’t Use Direct Formulas

If you use any functional formula directly on the links, then the Excel links won’t work unless the reference Workbook is open.

In the image below, cell D4 contains a link to a range in a sheet in Workbook1.xlsx. The OFFSET function has been applied directly to the link. With Workbook1 open the function works, and displays the correct result.

But when Workbook1 is closed, the function stops working and displays an error instead.

Solution:

Don’t apply formulas directly to your links, or reference the cell values of the linked range only, and apply any functions to these values as a subsequent step. The links will then work even when the reference Workbook is not open.

With Workbook1 closed, the link in cell C5 is still working perfectly.

Now Excel functions can be applied to the linked cell values with Workbook1 closed. Here for example, using the IF function.

Solution 2 – Update the Name of the Linked File

If you mistype or change the file name of a linked Workbook, then the link won’t work. You will get the following warning from Microsoft Excel.

Solution:

Check your inputs, and if you change any linked file’s name, update the Workbook name in the links accordingly.

Solution 3. Apply Update Values

If the previous fixes don’t work, or if any value in the linked workbook changes, update the values in your worksheet using the Edit Links feature.

Steps:

From the Data tab >> click the Edit Links feature.

A dialog box named Edit Links will appear.

Choose Update Values >> click on Close.

In this window, you can also check the status of the link by using the Check Status option.

Solution 4 – Must Select Update Option

When you re-open an Excel file which has a link to another workbook, the following warning from Excel will be displayed. Always select Update to make the links work.

Solution 5 – Try to Not Use the Name of Reference Range

Sometimes, if you use a name of a reference range in your link, then the link may not work. Use direct cell references instead.

Solution 6 – Reopen Workbook

If you link to a workbook which has been exported from other file types, saving the linked workbook as a different Excel file type using the same name can solve problems with links. After renaming the file, close both workbooks and re-open them. The links should now be working normally, even after you close the linked Workbook.