Excel links not updating even with calculations set to automatic.

Question

Hello,

I have an issue where sometimes the links that I have in an excel document will not update when the values change. I have the document set to automatic calculations, and that has never changed until I started troubleshooting why this issue has occurred. I have a summary table for multiple sources, and each source has multiple data points that are linked to another worksheet within the same workbook (no external links to other excel documents). For some unknown reason, the links will periodically not update to the proper values that they should update to. I am looking for a solution to force recalculate everything outside the "Calculate Now" or "Calculate Sheet" option within the formula tab, or the reason as to why my calculations did not update in the first place.

I have added quotations (" ") around references to display exactly what is in my excel document, and are not in the cells, name manager, or tab name.

A) I have Cell G8 in tab "Total Site Emissions" set to the formula "=Flare1_CO_LPH", a name manager name. The Cell is displaying a value of 0.

(All the values that are a value of 0 on this sheet are not 0 on the cells that they are linked to. Cells C8, C9, D8, D9, E8, and E9 had the same problem, but I had clicked into them and "recalculated" them as stated in Step D)

B) Within the Name manager, it is displaying "18.82320" within the "Value" column. The formula the Name manager has for "Flare1_CO_LPH" is "='Flare Emissions 1'!$H$101"

C) When I go to "'Flare Emissions 1'!$H$101" in the "Flare Emissions 1" tab, the value is indeed 18.82320.

D) When I go into Cell G8 in "Total Site Emissions" tab and pretend as if I am editing the cell, and click enter to confirm my equation, the value updates from 0 to 18.82320 without making any changes to the current formula in the cell, just reconfirming what I already had in there.

I have hundreds of cells that have this same error with the same setup. I have also run into this same error with Cell G8 on "Total Site Emissions" being set to "='Flare Emissions 1'!$H$101".

I have tried swapping the calculation option to manual, saving and reopening. Setting calculation option back to automatic saving and reopening.

I have tried force saving the document to try and get excel to recalculate.

I have tried the "Calculate Now" and "Calculate Sheet" options.

I have confirmed that all the cells in question are formatted as numbers and not text.

I have confirmed that there are no extraneous ' or " or spaces within the formulas.

I have tried repairing my excel installation and reinstalling excel.

I have tried a different computer.

I have tried the online version of excel.

I have tried saving it as an .xlsx file (because it is an .xlsm file at the moment)

The ways I have gotten the value to update.

Clicking into the cell and clicking enter to recalculate it again (not changing the formula in the cell at all.)

Changing the "Flare 1 Emissions" Cell H101 value again. Once it changed again, it updated.

Recreating the entire situation with a new name manager name and link.

None of these solutions work for me because the initial link did not update for an unknown reason. I have too many things linked to other places to go into each cell and "recalculate" it. I need a solution that will allow me to "update" all the cells in every one of my worksheets easily, or prevent the problem initially.

I am on a laptop with the specs shown below:

CPU: 12th Gen Intel(R) Core(TM) i7-12800H

Memory: 32.0 GB 4800 MT/s DDR5

Storage: 1TB m.2 SSD

Integrated Graphics: Intel(R) Iris(R) Xe Graphics

GPU: NVIDIA RTX A3000 12GB Laptop GPU

Windows Version 23H2 (OS Build 22631.3880)

Microsoft Office Home and Business 2021

Microsoft® Excel® 2021 MSO (Version 2406 Build 16.0.17726.20078) 64-bit

Answer 1

Hi Stephen,

Thanks for reaching out to Microsoft Community. Is it specific to this document or general cases? What if you create a new sample file? If it's file specific, the problem should reside in your file, maybe it is corrupted or so.

I searched internet, it seems that many people encountered similar problems. Many people suggested F9 related shortcuts (F9, shift F9, ctrl alt F9) or just replace "=" with "=" to simulate enter data as a workaround. I wonder if the workaround is workable for you.

Best Regards,

Thomas.L - MSFT | Microsoft Community Support Specialist

Answer 2

Hello Thomas,

Thank you for your response!

I looked into F9 solutions, and looked at the Microsoft keyboard shortcut here: https://support.microsoft.com/en-us/office/keyboard-shortcuts-in-excel-1798d9d5-842a-42b8-9c99-9b7213f0040f

"Ctrl+Alt+Shift+F9: rechecks dependent formulas, and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated."

The formulas I assume were not marked to be calculated. I assume this portion, "including cells not marked as needing to be calculated", of the command fixed the problem. I am not sure why or how they got marked to not be calculated, but this keyboard shortcut fixed the problem.

Thank You again

Stephen