Forum Discussion
mbalb985
Jun 06, 2024Copper Contributor
Linked Formulas Revert to Obsolete Values AFTER Opening File.
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 Fi...
NikolinoDE
Jun 07, 2024Gold Contributor
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:
- Open File 2.
- Go to the Formulas tab.
- Click on Calculation Options and ensure it is set to Automatic.
3. Refresh All Data Connections
Sometimes, refreshing all data connections can help:
- Open File 2.
- Go to the Data tab.
- Click on Refresh All.
4. Check for Circular References
Circular references can cause unexpected behavior in calculations:
- Open File 2.
- Go to the Formulas tab.
- 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:
- Open File 2.
- Go to the Data tab.
- Click on Edit Links.
- 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:
- Close Excel.
- Go to %appdata%\Microsoft\Excel.
- 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:
- Open File 1 and File 2.
- Save each file with a new name (e.g., File1_New.xlsx and File2_New.xlsx).
- 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:
- Open File 2.
- Go to the cell with the external reference.
- 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:
- Open Excel.
- Go to File > Account.
- 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.