Excel is not updating cells, options > formula > workbook calculation set to automatic

Question

The usual answer to this question is to turn formulas to automatic. This, in my case, is not working.

The second usual answer is that there is some macro that is affecting the Excel settings. This is not the case with my problem (xlsx file).

Office 2010, my simple formula is not updating.

The formula is:

=IFERROR(TRIM(OFFSET(MainCopy!AG$3,$A77,0)),"") 

Everything else is updating in the spreadsheet. The cell in MainCopy has the correct value. The value displayed is the value before I updated the information on MainCopy. When I put new information in MainCopy, the value on this page stayed the same.

I have:

• Researched this issue on the internet - all solutions easy to find do not address this problem
• Pressed F9 (did nothing)
• Pressed Shift-F9 (did nothing)
• Turned calculation to manual and hit all combinations of F9 (did nothing)
• Turned calculation back to automatic (did nothing)
• Saved with a new name (did nothing)
• Re-updated data on MainCopy (did nothing)
• Selected the cell and hit F9 (did nothing)
• Put my cursor in the box and hit enter (UPDATED VALUE CORRECTLY)

I cannot put my cursor in each box and hit enter, there are too many boxes. In addition, this is creating a major error trap in a spreadsheet that needs to be correct. Is there some obscure setting that has been triggered in this file that is creating this issue?

Answer 1

I had this happen in a worksheet today. Neither F9 nor turning on Iterative Calculation made the cells in question update, but double-clicking the cell and pressing Enter did. I searched the Excel Help and found this in the help article titled Change formula recalculation, iteration, or precision:

CtrlAltF9:

Recalculate all formulas in all open workbooks, regardless of whether they have changed since the last recalculation.

CtrlShiftAltF9:

Check dependent formulas, and then recalculate all formulas in all open workbooks, regardless of whether they have changed since the last recalculation.

So I tried CtrlShiftAltF9, and sure enough, all of my non-recalculating formulas finally recalculated!