Excel doesn't update value unless I hit Enter

Thanasis picture Thanasis · Jul 8, 2015 · Viewed 98.2k times · Source

I have a very annoying problem in one of my worksheets in a workbook. I am using EXCEL 2007. Any cell's value will not be updated unless I hit ENTER. Either if the formula in the cell includes an if condition, or a VLOOKUP function or even an AVERAGE function. All the calculations are set to automatic, Application.Calculation = xlAutomatic, and even the calculations for the specific worksheet are enabled, like : ws.EnableCalculation = TRUE.

Furthermore, the ScreenUpdating is set to TRUE. After I hit the ENTER or I drag down the right corner, the cells will be updated, and they will keep being updated if I make any change. However, after saving the file and reopening it again they will be frozen again. I haven't figured out exactly when they will stop being updated again. All the formatting are set to General or number.

Especially in IF conditions, when I check the calculations through the evaluate feature, the result is correct but it is not passed on the screen.

Any suggestion? This thing is driving me crazy.

Answer

AndyGneiss picture AndyGneiss · Aug 25, 2016

Executive summary / TL;DR:
Try doing a find & replace of "=" with "=". Yes, replace the equals sign with itself. For my scenario, it forced everything to update.

Background:
I frequently make formulas across multiple columns then concatenate them together. After doing such, I'll copy & paste them as values to extract my created formula. After this process, they're typically stuck displaying a formula, and not displaying a value, unless I enter the cell and press Enter. Pressing F2 & Enter repeatedly is not fun.