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

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel Links Not Working Unless Source Workbook is 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.

Solution for Excel links not working unless source Workbook is open

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.

Use Exact Name of Linked File unless Excel Links Not Working even Source Workbook is Open

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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Musiha Mahfuza Mukta
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, has been working with the ExcelDemy project for 1.5 years. She has written over 97+ articles for ExcelDemy. Currently, she is working as the Excel and VBA Content Developer, creates contents, solves user problems, writes articles etc. Her work and learning interest varies from Microsoft Office Suites, and Excel to Data Analysis, and developing Excel Applications with VBA codes.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo