Help: Excel Formulas NOT udpating and NO it has NOTHING to do with automatic option or circular references, ETC. !!

[Donavan Casson]

OK, I am absolutely fit to be tied! I am a Microsoft 365 User and have an issue where certain formulas are not updating unless I go directly into the cell and hit enter. And NO, this has NOTHING to do with automatic option or circular references, or the format of the cell, etc. etc. and all of the other what seems like thousands of possibilities posted out there on the internet. I am completely fed up of reading article after article suggesting the same things over and over that have ZERO to do with my issue. Please advise and help. Thank You.

 

[Fluff]

Very difficult to advise when we don't know what

etc. etc. and all of the other what seems like thousands of possibilities posted out there on the internet

actually entails.
Would you be able to share your workbook?

 

[6StringJazzer]

OK, so your problem does not fit any of the thousands of problems that anyone actually knows how to solve.

Does this happen on every file, or just this one file? Are you using the desktop version or web version?

If it is just this one file, is there any way for you to share it (DropBox, OneDrive, etc.)? If you have a unique problem it will be difficult to troubleshoot without your file.

 

[Donavan Casson]

OK, so your problem does not fit any of the thousands of problems that anyone actually knows how to solve.

Does this happen on every file, or just this one file? Are you using the desktop version or web version?

If it is just this one file, is there any way for you to share it (DropBox, OneDrive, etc.)? If you have a unique problem it will be difficult to troubleshoot without your file.

Thanks for the reply. I have only witnessed this problem in one file. That said it is a personal finance file which is mainly the only file I use on my home laptop. So I can't be certain this would occur in other files. Also, unfortunately the problem is intermittent and does not occur all the time...uggh.. seems impossible to trouble shoot. I am using the desktop version of Microsoft Office 365 that I downloaded and installed on my machine. The other issue is it occurs on different worksheets, etc. I could perhaps save a redacted watered down version of this through DropBox.

I did not review thousands of problems on the internet...that was hyperbole and am just frustrated because I literally see a few common items in every article such as the automatic function needs to be enabled, the circular reference problem, and a space next to the equal sign, etc....but none of those are my problem. Any ideas from a systems / windows or excel perspective on this?

Thanks

 

[Kenobi1]

Is the file in question VBA based ?

If so check the code to see whether
ActiveWorkbook.RefreshAll has been set to "False"

Cheers
Des

 

[Dan_W]

I agree with Kenobi - I was fighting the temptation to say "It sounds like a circular reference... yep, definitely a circular reference", but it is more than likely that the file is a macro-enabled workbook and it is either the possibility that Kenobi mentioned, or
Application.Calculation = xlCalculationManual
or
Application.ScreenUpdating = False

But in my heart of hearts, I just know it's because it's a circular reference... ?

 

[Donavan Casson]

Very difficult to advise when we don't know what actually entails.
Would you be able to share your workbook?

Unfortunately this is a personal finance workbook and I can't share. I was exaggerating some when I said "thousands of possibilities...". The main reasons I have seen out there on the internet for this to be happening are:

- Circular reference issues
- Calculation set to manual instead of automatic
- An error in the formula where there is a space before or after the equal sign
- Cell is formatted is text
- Show Formula is turned on

I see these five possibilities listed everywhere. NONE of them represent my issue...I have double and triple checked.

 

[Donavan Casson]

Is the file in question VBA based ?

If so check the code to see whether
ActiveWorkbook.RefreshAll has been set to "False"

Cheers
Des

This is not a VBA file, but I did check the Refresh All option anyway and only one option was available and checked and that is, "Refresh All". All other options were grayed out.

 

[Donavan Casson]

I agree with Kenobi - I was fighting the temptation to say "It sounds like a circular reference... yep, definitely a circular reference", but it is more than likely that the file is a macro-enabled workbook and it is either the possibility that Kenobi mentioned, or
Application.Calculation = xlCalculationManual
or
Application.ScreenUpdating = False

But in my heart of hearts, I just know it's because it's a circular reference... ?

This is not a VBA file nor is it one that is macro enabled and I've double and triple checked for a circular reference and that isn't it. In fact in excel when there is a circular reference problem as I'm sure you are aware you'll get a message as such usually down in the bar at the bottom of the screen. This is not occurring.

Separately I had a few add-in's installed for a software that I no longer use and have removed those add-in's and maybe will get lucky they could have been causing the problem. I just don't know. I'm almost to the point of uninstalling and re-installing Microsoft Office 365 to see if that helps.

 

[Dan_W]

I was teasing re: circular referencing - that's how I tend to react to messages in ALL CAPS.
Two things:

(1) It is easy enough to see whether or not a sheet is engaged in circular referencing. As I'm sure you've already discovered from you research, Exel will inform the user if there is any in the workbook. In case anyone else reading this post needs to know, this can be found out by clicking the "Formulas" tab and then clicking on "Error Checking" in the 'Formula Auditing' group.

(2) Rethinking your original post and now (pretty much supported by your last message), it may be the case that it's not that particular file that's causing the problem, but another. It is entirely possible that one of the add-ins is causing the problem.

If one of the addins (or any other macro-enabled file you loaded) turns off the screen-updating property (or here, likely changes the calculation method) and does not later revert to the default setting, this will affect every other spreadsheet. It may be the case that the add-in/macro intended to revert to the default setting, but this was interrupted by an error and execution of the code was also interrupted, thus causing the problem.

In any event, Excel will revert to default when you restart it. If the problem persists (as it appears to be in your case) and the add-ins are loaded, then yes - it is more than likely that cause of the Add-ins. I suspect that by now you've tried to disable/uninstall the add-ins? How did it go?

I would make two more observations - if you have tried coding a macro in the past, then the code may have been saved in your Personal Workbook. Also, for the benefit of anyone else, it is worth noting that although macro-enabled workbooks are (these days) indentifiable by the extension -> XLSM, XLTM, XLAM (though XLAM is not technically a workbook) - it's important to remember that XLS files can also contain macros (as can XLA, obviously).