[Fixed!] Excel Links Not Updating Unless Source Is Open

In Microsoft Excel, working with multiple workbooks is a common task. You may have connections or links between these workbooks. It helps to visualize the changes if there are some changes in the source file. the main purpose of this file linking is to update the file automatically. But if for any reason, the files are not updating while the source file is open. It can create irritation. If you are curious to know how you can solve the issue of Excel links not updating unless the source is open, then this article may come in handy for you. In this article, we discuss how you can solve the issue of how we issue Excel links to updating unless the source is open with an elaborate explanation.

Download Practice Workbook

Download this practice workbook below.

5 Possible Solutions If Links Are Not Updating Unless You Open Source in Excel

We are going to use the below dataset to demonstrate how you solve the issue of links not updating unless the source is open. In the dataset, we have product information like Quantity and Cost.

And another part of the dataset contains Revenue and Profit.

1. Update Values in Your Spreadsheet

The easier and most straightforward method of solving this Excel Links not updating unless the source open issue is to update the values through the Edit Links. It is possible to do this with the Edit Links command or by reopening the file.

1.1 Update Values Through Edit Links Command

Updating can be done primarily through the Edit Links in the Queries and Connections group.

Steps

• First, we need to link one dataset with another one.
• To do this, we open two datasets, one dataset’s name is Destination.

• And another dataset name is Source.

• To link them, first select cell E4 in the Destination workbook.
• And then switch to the Source workbook.
• From there, select cell C4
• While you select C4, a formula will generate in the formula bar in E4 in Destination workbook
• Pressing Enter will link cell D4 (Source) with cell E4 (Destination).
• Then carefully remove the $ sign from the formula.

• Then drag the Fill Handle to cell C10.
• Or you could enter the following formula and drag the Fill Handle to cell C10.

The range of cell C4:C10 is now linked

• Do the same for the Profit column.

• Then drag the Fill Handle to cell C10.

• Now the whole range of cell E4:F10 is now linked with the Source workbook.
• As we alter the values of the Revenue and Profit column in the Source workbook, the corresponding column values are also changed in the Destination workbook.
• But both of these files need to be open in order to update the values.
• One solution is to use update values options from the Edit Links.

• Click on the Check Status.

• Now, if you close this dialog box, open the Source workbook again, and click Check Status, then you will notice the status is now showing that the Source is open.

• After this, close the Source file, and then go to the Edit Links dialog box again.
• As you click on the Check Status, the status now changes to OK
• This means the file is now available in the directory in the link, but the file is not open.
• Although the file is not open, you can still update the values by clicking Update Values in the dialog box.
• Click on Close after this.

• Before closing the file, we input 3900 on cell D4 of the source workbook as shown in the image below. But the value didn’t update in the Destination workbook as the file was closed.
• After clicking on the Update Values, the values updated to 3900, as shown in the picture.

1.2 Update Values While Reopening File

Apart from the Edit Links command, we can also completely shut down the Excel and then reopen the file. Which will update the links automatically. Although using the Edit Links command is more useful.

Steps

• If you have a file named Source that is linked with the Destination workbook, then the values are supposed to get updated automatically.
• But if the source file is not open, then the Destination file won’t update automatically.
• The solution is to reopen the file.
• When you reopen the file, Excel will look for the links that need to be updated and will update them if required.
• Right after you reopen the file, the below warning box will appear.
• Click on the Update to update all the existing links in the file.
• This will update the links even if the Source file is in a close situation.

Read More: How to Update Links in Excel (5 Easy Methods)

2. Remove Named Ranges

The named ranges can create a lot of issues while updating the values in the Excel sheet.

Steps

• If you have tables in the Source workbook, and you try to link any of the cells in the Source workbook in the Destination workbook as named range as shown in the image.

• Then you could get #REF! error.

• To avoid this, always try to delete the named range or convert tables to range data.
• Then link the cells in the range of cell format. Like in this case, instead of the Profit named range, delete that named range, and use an absolute cell reference.
• Like we used reference $D$4 instead of that named range.

3. Investigate Saved Location of Source File

File no saving in the correct directory can cause the destination file unable to update the values.

Steps

• If you have linked files that are not updating, the reason could be that the files have been moved to another folder or the file is deleted.
• To investigate, go to the Edit Links in the Formulas tab.

• In the Edit Links window, if you click the Check Source, then you can see that the status is showing “Source not found”.

• You can resolve this issue by clicking Change Source.
• From this option, you can relocate the files which are in a new location.
• Then the status will change to OK.
• Click Update Values and click Close after this.

Read More: How to Update Links Without Opening File in Excel (4 Methods)

4. Set Calculation Options to Automatic

If the Workbook calculation settings in Excel are manual, then it can create problems while data updating. In order to avoid this, we need to set it to automatic.

Steps

• For this, click on the FIle.

• Then click on Options.

• In the Options, click on Formulas.
• Then notice whether the Manual is set in the Calculation Options.

• If Manual is already in the selection, then switch the selection to Automatic.
• Click OK after this.

Read More: [Fixed!] Excel Update Links Manually Greyed Out

5. Disable Table Names in Formulas

If you have a table in your Source workbook and you want to link that workbook with the Destination workbook. Then the range of columns is automatically changed to a named range.

Steps

• As we know from the previous method, these named ranges could create problems.
• To avoid this situation, we need to disable table auto calculation.
• For this, click on the File tab.

• Then click on Options.

• In the Options, click on Formulas.
• Then notice whether the Use table names in formulas check box is ticked.

• If the box is ticked, then untick the box and then click on OK.

Read More: How to Disable Automatic Update of Links in Excel (3 Easy Ways)

Conclusion

To sum it up, the issue of how we issue Excel links to updating unless the source is open here in 5 different ways.

For this problem, two separate workbooks are available to download where you can practice these methods.

Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.