Linked Formulas Revert to Obsolete Values AFTER Opening File.

[mbalb985]

I have an issue where values in a spreadsheet revert to previous values AFTER the file has been reopened, and has been worked in for some time.    For example: Supplies costs are calculated in one File 1, and is referred to in File 2.  If we update File 1, the value in File 2 updates and we save.  Upon re-opening File 2, the value reflects the new, correct value, but if you perform certain actions (copy a tab, re-save the file), the value reverts to the previous amount.  It's odd because I would more understand if it showed the old value upon opening, and then updated as it recalculated, but this is the opposite.  It shows the correct value upon opening, and then reverts to an old value that doesn't even exist in the linked file. 

Any thoughts on what causes this, and how to fix? 

[#2]

@mbalb985 

This issue can be caused by a few different factors, including the use of external links, Excel's calculation settings, and potentially corrupted links or files. Here are some steps to diagnose and fix this problem:

1. Check and Update Links

Ensure that the links between your files are correctly updated:

• Open File 2.
• Go to the Data tab.
• Click on Edit Links.
• Select the source file (File 1) and click Update Values.
• Click Close and save File 2.

2. Verify Calculation Settings

Make sure Excel is set to automatically update calculations:

1. Open File 2.
2. Go to the Formulas tab.
3. Click on Calculation Options and ensure it is set to Automatic.

3. Refresh All Data Connections

Sometimes, refreshing all data connections can help:

1. Open File 2.
2. Go to the Data tab.
3. Click on Refresh All.

4. Check for Circular References

Circular references can cause unexpected behavior in calculations:

1. Open File 2.
2. Go to the Formulas tab.
3. Click on Error Checking and select Circular References to identify and resolve any circular references.

5. Inspect and Fix Broken Links

If there are broken links, they might cause the values to revert:

1. Open File 2.
2. Go to the Data tab.
3. Click on Edit Links.
4. Look for any broken links and update or remove them as necessary.

6. Use INDIRECT Function with Caution

If you are using the INDIRECT function to refer to cells in another workbook, ensure that both workbooks are open simultaneously. INDIRECT does not work well with closed workbooks.

7. Clear Old Cache

Clearing the Excel cache might help if old values are being stored incorrectly:

1. Close Excel.
2. Go to %appdata%\Microsoft\Excel.
3. Delete the .xar and .xlb files (Excel will recreate them as needed).

8. Save As New Files

Try saving both files as new files to see if this resolves any potential corruption:

1. Open File 1 and File 2.
2. Save each file with a new name (e.g., File1_New.xlsx and File2_New.xlsx).
3. Update the links in File2_New.xlsx to refer to File1_New.xlsx.

9. Rebuild the Links

If the above steps do not work, consider manually rebuilding the links:

1. Open File 2.
2. Go to the cell with the external reference.
3. Manually re-enter the reference to ensure it is correctly pointing to the updated value in File 1.

10. Office Updates

Ensure your Microsoft Office suite is fully updated as there might be patches or updates that resolve such issues:

1. Open Excel.
2. Go to File > Account.
3. Click on Update Options and select Update Now.

By following these steps, you should be able to diagnose and fix the issue with linked formulas reverting to obsolete values after opening and working with the file. The text and steps were edited with the help of AI.

If none of these steps help you, I recommend adding more information to your topic. Information such as Excel version, operating system, storage medium, file extension, etc.

In this link you will find some more information about it:

Welcome to your Excel discussion space!

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.