Sometimes, you may face problems like Excel links not working unless the source workbook is open. In this article, I’m going to demonstrate the solutions to Excel links not working unless the source workbook is open.
Excel Links Not Working Unless Source Workbook Is Open: 7 Solutions
Here, I will explain 7 possible solutions to Excel links not working unless the source workbook is open. For your better understanding, I’m going to use two workbooks. The names of the workbooks are Workbook1 and Workbook2.
Now, let’s talk about the solution.
1. Using Direct Formula
If you use any functional formula in the links then the Excel links won’t work unless the reference Workbook is open. Here, as you can see I have used a link by keeping reference as the Workbook1.xlsx. And, I have used the data range of the worksheet named myWorksheet from the Workbook1. In addition, I have attached an image of using the OFFSET function with the link. Also, the link is working but here I have kept the file named Workbook1 open.
But when you close the file named Workbook1 then the function will not work. As you can see in the below.
Solution:
That is why you should use either a direct formula or try to input the cell values only. After that, you can apply any function to them. Then you will get Excel links working even when the reference Workbook is not open.
Furthermore, below you can see the link is working nicely even when I have kept the Workbook1 closed.
Moreover, you can apply Excel functions with those cell values. Here, I have given an example below using the IF function.
2. Use the Exact Name of Linked File
If you change the file name then the link won’t work. In that case, you will get the following warning from Microsoft Excel.
Solution:
So, if you change any file name which you have used in the link as a reference then you must update the Workbook name in the link according to the file name.
3. Apply Update Values
Sometimes the previous solutions still may not work for the Excel links not working unless the source workbook is open. Then, you should update the values in your worksheet. Also, if you changed any value of the linked workbook then you should use the Edit Links feature.
Solution:
- Firstly, from the Data tab >> you should go to the Edit Links feature.
At this time, a dialog box named Edit Links will appear.
- Then, choose Update Values >> after that click on Close.
Furthermore, with this window you can also check the status of the link by using the Check Status option.
4. Must Select Update Option
When you reopen the Excel file which has a link with another workbook. Then you will see the following warning from Microsoft Excel and you should press Update to make the link work.
5. Try to Not Use the Name of Reference Range
Sometimes, if you define a name to the reference range which you are going to use in Excel links then the link may not work. In that case, you should avoid using any name of the range.
6. Reopen Workbook
You can try another way which is to save the linked workbook as any other types of Microsoft Office Excel using the same name. After that, you have to close both workbooks and open them again. This time you may see that the link is working. This method is most effective for the cases when you link with a workbook which is exported from other file types.
Read More: [Fixed!] ‘This workbook contains links to other data sources’ Error in Excel
7. Set Calculation to Automatic
If the Calculation Options in Excel remain in Manual mode then the Excel links may not work unless the source workbook is open.
For Excel links working with keeping source Workbook closed, you should keep the Excel Calculation Options in Automatic mode.
Solution:
- Firstly, from the Formulas tab >> go to Calculation Options
- Secondly, see which one is marked.
- Thirdly, if the Automatic option remains unchecked then you must check this option.
Read More: [Fixed!] This Workbook Contains Links to One or More External Sources That Could Be Unsafe
💬 Things to Remember
- You should avoid using Excel functions with the Excel links.
- Also, you must always use the same name inside the link according to the Workbook and Worksheet name.
Download Practice Workbook
You can download the practice workbook from here:
The file given below contains the Excel links.
Also, the reference file is given below.
Conclusion
I hope you found this article helpful. Here, I have explained 7 solutions for working Excel Links while Source Workbook is open. Please, drop comments, suggestions, or queries if you have any in the comment section below.
