My formulas don't refresh automatically

Copper Contributor

Hi, I have the following problem (I tried google and this forum but found nothing):

 

I have an external software writing text values to my excel column, and it's working fine. I have around 1400 rows filled by it. I have another column to convert each of these values to General (left is text, right is general:

Wojciech_Plonka_0-1616510172288.png

I am using the following formula:

 

Wojciech_Plonka_1-1616510217260.png

 

The problem is: in the past, everything was working fine, but suddenly my columns can't be refreshed. So, when those 1400 rows are filled with new text values, converted columns remain the same, as below:

 

Wojciech_Plonka_2-1616510328697.png

 

It gets refreshed when I click on that 0.264 value twice and press enter, then the value is good, but in the past it was done automatically and I didn't have to press enter 1400 times.

Any ideas on how to solve that issue? I have automatic calculation turned on and all other things are correct as well.

Thank you,

 

12 Replies

@Wojciech_Plonka 

Solution: The method used by Excel to calculate formulas depends on the settings you have made under "Tools, Options" on the "Calculation" tab.

If the "Automatic" option is activated there, Excel automatically recalculates all formulas in the entire workbook each time a cell is entered.

 

Attention: By pressing the F9 key you activate the manual calculation option.

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote any reply if it helps please, as it will be beneficial to more Community members reading here.

 

 

@NikolinoDE I tried all google-proposed solutions, including that one, and nothing helps:

Wojciech_Plonka_0-1616512210492.png

 

@Wojciech_Plonka 

It seems as you don't need any formula to convert the Textual Values into Real Values.

SELECT ALL 1,400 cells
> in the user interface click DATA > TEXT TO COLUMNS > FINISH.

-------------------------------------------- 
Michael (Micky) Avidan
“Microsoft®” MVP – Excel (2009-2022)
ISRAEL

Thank you for the response.

It is useful in general, but in this case it doesn't help: I need that particular fields to be working fine because they are affecting other fields, and other fields are not refreshing fine as well
Numbers formatted as text
Solve, in that case you just have to mark all affected cells with the mouse.
Then you click with the right mouse button on the marker.
Select Format cells from the context menu.
In the following window choose from the formats standard or number.
Confirm with OK and Excel should do the math again.
I'm not sure why your formulas are not re-calculating, but can you force excel to recalculate the workbook using Ctrl+Alt+Shift+F9?
doesn't work
it is the only thing that works so far, thank you! But if anyone knows a way for this to be done automatically please let me know
Try VBA, it may be the solution for you.
The automatic calculation in the excel workbook should be activated every time it is opened.

VBA Code:
Private Sub Workbook_Open()
With Application
.Calculation = xlAutomatic
.CalculateBeforeSave = False
End With
End Sub

Always at your services

Nikolino
it works for me. I had the same problem. Thanks
Thanks @JMB17. This worked for me. I've been searching on and off for a solution for a few weeks now, for the same situation: formulas not refreshing automatically. In my case it only seems to affect formulas that reference text in cells on other sheets, and only in one workbook. The Excel settings are correct (Automatic calculation), and I even tried "F9" and "Shift+F9" with no success. Until now I've had to F2 each cell and hit Enter. Using "Ctrl+Alt+Shift+F9" is a good work around, but I would REALLY like to know how to fix the problem.

@JMB17 

Yes, it's working, Thanks for the solution