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.
Read More: [Fixed!] ‘This workbook contains links to other data sources’ Error in Excel
Solution 7 – Set Calculation to Automatic
If the Calculation Options in Excel are set to Manual mode, then the Excel links may not work unless the source workbook is open.
The solution is to set the Excel Calculation Options to Automatic mode.
Steps:
- From the Formulas tab >> go to Calculation Options.
- Observe which one is marked.
- If the Automatic option remains unchecked, check this option.
Read More: [Fixed!] This Workbook Contains Links to One or More External Sources That Could Be Unsafe
Things to Remember
- Avoid using Excel functions directly on your links.
- Always use the correct name and location of the linked Workbook and Worksheets in your links.
Download Practice Workbook
Also, the reference file is given below.
Related Articles
- How to Link Two Workbooks in Excel
- Reference from Another Excel Workbook Without Opening
- Link Excel Workbooks for Automatic Update
<< Go Back To Linking Workbooks in Excel | Linking in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
