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

Here are 5 possible solutions for solving the problem of Excel links not updating unless the source files are open.u

We’ll use the following dataset, which contains a table with product information Quantity and Cost for some Product IDs.

Another table in the dataset contains Revenue and Profit for the same Product IDs.

Method 1 – Update Values in Your Spreadsheet

The easiest and most straightforward method of solving this issue is to update the values through the Edit Links command, or by reopening the file.

First, let’s link one dataset with another.

Steps:

• Open a dataset named Destination.

• Open a second dataset named Source.

• To link them, select cell E4 in the Destination workbook.
• Switch to the Source workbook.
• Select cell C4.
• A formula will be generated in the formula bar in cell E4 in the Destination workbook.
• Press Enter. Cell D4 (Source) is linked with cell E4 (Destination).
• Remove the $ sign from the formula.

• • Alternatively, enter the following formula in cell E4:

  =[Source.xlsx]Dataset!$C4

• Drag the Fill Handle down to cell C10.
• The range of cells C4:C10 is now linked.

• Repeat the process for the Profit column.

• Drag the Fill Handle to cell F10.

The whole range of cells 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. Let’s resolve this.

1.1 – Update Values Through Edit Links Command

STEPS:

• Click the Data tab in the ribbon.
• Click the Edit Links button.

• An Edit Links window opens. Status displays as “Unknown”.
• Click Check Status.
• Click Close.

• Open the Edit Links window in the Source workbook again.
• Click Check Status,
• Status is now showing that the Source is open.

• Close the Source file.
• Open the Edit Links dialog box again.
• Click on Check Status. The status changes to OK, indicating that the file is available even while not open.
• Although the file is not open, values can be updated by clicking Update Values in the dialog box.
• Click Close.

Before closing the file, we input 3900 on cell D4 of the Source workbook, However, the value didn’t update in the Destination workbook as the file was closed.

After clicking Update Values, the values update correctly in the Destination workbook.

1.2 – Update Values While Reopening File

We can also completely close Excel and then re-open the file, which will update the links automatically.

Steps

• Close all open workbooks and exit Excel.
• Re-open the Destination workbook.
• A warning box appears.
• Click Update to update all the existing links in the file.

Links are updates regardless of whether the Source file/s are open or closed in Excel.

Method 2 – Remove Named Ranges

Named ranges are often the culprit preventing linked values from updating. Removing them can solve the problem.

If you try to link any of the cells in the Source workbook with the Destination workbook as a named range…

… a #REF! error may be returned.

To prevent this, delete the named range or use an absolute cell reference.

In this case, delete the Profit named range, and use reference $D$4 instead.

Method 3 – Update Saved Location of Source File

Source file not found in the expected directory will cause the Destination file to be unable to update values.

Steps

• Click Edit Links in the Data tab.

• In the Edit Links window that opens, if you click Check Status, Status displays “Source not found”.

• Click Change Source.
• Locate and select the file in its correct location..
• Status will change to OK.
• Click Update Values.
• Click Close.

Method 4 – Set Calculation Options to Automatic

If the Calculation settings in Excel are set to Manual, it can create issues updating data from linked sources.. Changing this setting to Automatic can resolve them..

Steps

• Click the File tab.

• Click Options. The Excel Options window opens.

• Select Formulas.
• Under Calculation Options, select Automatic.

• Click OK.

Method 5 – Disable Table Names in Formulas

Disabling Table auto calculation can sometime resolve issues with data from linked sources not opening while the Source file is closed.

Steps

• Click the File tab.

• Click Options. The Excel Options window opens.

• Click Formulas.

• Under Working with Formulas untick Use table names in formulas.
• Click OK.

Download Practice Workbooks

Related Articles

• How to Update Hyperlink in Excel Automatically
• [Fix]: Excel Automatic Update of Links Has Been Disabled
• How to Update Links Without Opening File in Excel
• How to Disable Automatic Update of Links in Excel

<< Go Back To Update Excel Links | Linking in Excel | Learn Excel