24

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.

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
Thanasis
  • 695
  • 1
  • 4
  • 17
  • 1
    If calculation is definitely not being disabled, then I'd guess you have a circular reference problem. – Rory Jul 08 '15 at 13:28
  • I checked all sheets and non of them has the Circular Reference at the status bar next to Ready. Moreover, the Check Circular Reference option is disabled in the Formula tab – Thanasis Jul 08 '15 at 13:39
  • Is it only that one worksheet? If so, have you ever used `EnableCalculation` on that sheet to disable calculations? – Rory Jul 08 '15 at 13:43
  • I have used this flag but now even if I set in all worksheets this flag to True, the problem isn't resolved. – Thanasis Jul 08 '15 at 13:55
  • I have heard before of instances where using that property caused problems. I'd try recreating that sheet. – Rory Jul 08 '15 at 13:56
  • I will try to do that then. Do you know how can I isolate a number of worksheets and prevent from calculating when I don't need them? Because some of them are pretty heavy and take ages to calculate but I don't use them. – Thanasis Jul 08 '15 at 14:14
  • That's a whole separate question, and the answer will depend on the nature of your model. – Rory Jul 08 '15 at 14:19
  • It seems that this flag was the problem. I have the same problem with that guy here: http://www.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. My other problem is that I have a sheet (sheet2) with more than 10,000 cells which are formula driven based on values in another sheet (sheet1). By making any change in sheet1 it takes ages to update sheet2, but I don't want to use the values in sheet2 yet. So I want to isolate them for a while. – Thanasis Jul 08 '15 at 14:42

9 Answers9

29

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.

AndyGneiss
  • 391
  • 3
  • 5
  • 2
    You saved my life. Thanks for the jugaad – Taha Rushain Nov 16 '17 at 18:22
  • 2
    Such an excellent and simple solution, works in Excel 2016. I created this as a Macro to do it automatically: `Sub InternalRefresh() Cells.Replace What:="=", Replacement:="=", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False End Sub` – Matt Jul 26 '18 at 07:47
  • This worked for me, but I still do not understand why it was necessary. This appears to be a bug in Excel. – Raj Mar 10 '19 at 22:56
  • This worked for me, wish I could have solved it programmatically though. – isopach Jun 14 '19 at 12:19
  • Thanks .. finally! It is still a hand action, but at least I can do it in one go. I had date values "22/03/2022" that don't want to format as "2022/03/22". Instead of "=" I therefore replaced the "/" by "/" .. since the values all have this char. – Relaxed1 May 10 '22 at 11:15
16

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

Location in Ribbon

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.

Carl Kevinson
  • 794
  • 4
  • 16
  • 7
    No, as I have said, the calculations are set to Automatic, so the issue is not there. Thanks however for the prompt reply. – Thanasis Jul 08 '15 at 13:26
  • Menu bar looks bit different on recent Mac versions (with the dark green background), but approximately the same place. Myself and a coworker thank you! – Mark Bennett Jan 23 '17 at 23:00
  • This is not answering the question. You would THINK it does... but it doesn't. – ikku100 Mar 24 '22 at 14:57
9

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.

Kamol Singh
  • 91
  • 1
  • 1
6

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.

Mathew
  • 61
  • 1
  • 1
4

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.

Thanasis
  • 695
  • 1
  • 4
  • 17
4

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

user9742503
  • 41
  • 1
0

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

Alex Chuan
  • 1
0

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)
java-addict301
  • 3,220
  • 2
  • 25
  • 37
0

Found a simple solution.

Let's say once cell A1 is changed, so you expect cell B1 change the color by conditional formula.

Make the problematic cell - B1 has a formula with dependency to cell - A1

So, for example instead of my cell B1 just have text value "Text", i'll change it to formula like:

=IF(A1>0,"Text","Text") - means: if A1 > 0, then insert value "Text" to B1

The result is - each time cell A1 is recalculated, it forces also cell B1 color to be updated.

Adir Dayan
  • 1,308
  • 13
  • 21