Excel doesn't update value unless I hit Enter

问题:

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.

回答1:

It sounds like your workbook got set to Manual Calculation. You can change this to Automatic by going to Formulas > Calculation > Calculation Options > Automatic.

Manual calculation can be useful to reduce computational load and improve responsiveness in workbooks with large amounts of formulas. The idea is that you can look at data and make changes, then choose when you want to make your computer go through the effort of calculation.

回答2:

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.

回答3:

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

回答4:

This doesn't sound intuitive but select the column you're having the issue with and use "text to column" and just press finish. This is the suggested answer from Excel help as well. For some reason in converts text to numbers.

回答5:

Select all the data and use the option "Text to Columns", that will allow your data for Applying Number Formatting ERIK

回答6:

Found the problem and couldn't find the solution until tried this.

1. Open Visual Basic from Developer tab (OR right-click at any sheet and click 'View code')
2. At upper left panel, select 'ThisWorkbook'
3. At lower left panel, find 'ForceFullCalculation' attribute
4. Change it from 'False' to 'True' and save it

I'm not sure if this has any side-effect, but it is work for me now.

回答7:

I Encounter this problem before. I suspect that is some of ur cells are link towards other sheet, which the other sheets is returning #NAME? which ends up the current sheets is not working on calculation.

Try solve ur other sheets that is linked

回答8:

I ran into this exact problem too. In my case, adding parenthesis around any internal functions (to get them to evaluate first) seemed to do the trick:

Changed

=SUM(A1, SUBSTITUTE(A2,"x","3",1), A3) 

to

=SUM(A1, (SUBSTITUTE(A2,"x","3",1)), A3)