Excel does not update value unless I hit Enter

Question

I have a very nasty problem in one of my worksheets in a book. I am using EXCEL 2007. Any cell value will not be updated unless I press ENTER. Or if the formula in the cell includes an if condition, or the VLOOKUP function, or even the AVERAGE function. All calculations are set to automatic, Application.Calculation = xlAutomatic, and even calculations for a specific sheet are included, for example: ws.EnableCalculation = TRUE.

In addition, ScreenUpdating is set to TRUE. After I press ENTER or by dragging the right corner, the cells will be updated, and they will be updated if I made changes. However, after saving the file and opening it again, they will be frozen again. I definitely did not understand when they would stop updating. All formatting is set to Total or number.

Especially in IF conditions, when I check the calculations using the evaluation function, the result is correct, but it is not displayed on the screen.

Any suggestion? This thing is driving me crazy.

Answer 1

Summary / TL; DR:
Try to find and replace "=" with "=". Yes, replace the equal sign with yourself. In my script, he made everything update.

Background:
I often make formulas in several columns, then combine them together. After that, I copy and paste them as values ​​to extract my created formula. After this process, they usually get stuck, displaying the formula and not displaying the value, if I do not enter the cell and press Enter. Pressing F2 and Enter several times is not fun.

Answer 2

It looks like your book was set up for manual calculation. You can change this to Automatic by choosing Formulas> Calculation> Calculation Parameters> Automatically.

Manual calculation can be useful to reduce the computational load and improve response in books with a large number of formulas. The idea is that you can view data and make changes, and then choose when you want your computer to go through the calculation effort.

Answer 3

I have the same problem with this guy: mrexcel.com/forum/excel-questions/318115-enablecalculation.html Application.CalculateFull sold my problem. However, I am afraid if this happens again. I will try not to use EnableCalculation again.

Answer 4

I have come across this problem before. I suspect some of ur cells are linked to another sheet, which other sheets return #NAME? which ends, the current sheets do not work in the calculation.

Try to solve other sheets related to

Answer 5

It doesn’t sound intuitive, but select the column you are facing and use “text for column” and just click “Finish”. This is also a suggested answer from Excel Help. For some reason, converts text to numbers.