Excel (2019 and 365) not updating formulas, only when editing/entering cell

[PeteWright]

Hi everybody,

my Excel (2019 and 365, haven't tested 2016 yet) is not updating formulas, only when I enter (with F2) and save (with Enter) the cell.
As you can see in the screen capture, the formulas and conditional formatting are only updating when I manually update the cell.

What could be the reason for this? I already have automatic update for formulas enabled.

Any help appreciated.
Pete

 

[RoryA]

Assuming they are built-in Excel formulas, not UDFs, and if calculation is indeed set to Automatic, it would appear to be a glitch. I'd restart Excel.

 

[PeteWright]

Assuming they are built-in Excel formulas, not UDFs, and if calculation is indeed set to Automatic, it would appear to be a glitch. I'd restart Excel.

Thanks for the feedback.
- They are built-in formulas
- calculation is set to Automatic
- I already restarted Excel several times

May the problem be caused by different Versions of Excel?
At work I use Excel 365, on my laptop at home Excel 2019 and on my home desktop Excel 2016.

The data inside the yellowish cells was pasted from a tab-delimited text file, where some columns are numbers and other are text-only.
So, may that be the cause?
Columns C to G contain the imported data, where G is Text-only.
The problem is, that everything updates only when "changing" cells in column G

Any ideas?

 

[RoryA]

What are the formulas? It sounds like they are ones that ignore text and so it's not until you re-enter the numeric data that it actually becomes a number, and therefore the formulas update accordingly.

 

[PeteWright]

J138 = IF( RIGHT(C138;2)="01"; SUMPRODUCT( 1 * ($B$5:B1137 = B138 ) * (RIGHT(C138;2)="01") ); "" )
K138 = IF( RIGHT(C138;2)="01"; SUMPRODUCT( 1 * ($B$5:B1137 = B138 ) * ($G$5:G1137 = "727") * (RIGHT(C138;2)="01") ); "" )
L138 = IF( RIGHT(C138;2)="01"; IF( G138="727"; SUMPRODUCT(($G$5:$G$1004="727") * ($B$5:$B$1004=B138) * ($F$5:$F$1004) * (RIGHT(C138;2)="01")) / K138; IF( LEFT(G138;3)="726";F138;"") ); "" )
M138 = IF( RIGHT(C138;2)="01"; IF( SUMPRODUCT( ($B$5:$B$1004=B138) * ($G$5:$G$1004="540") * 1); SUMPRODUCT(($G$5:$G$1004="540") * ($B$5:$B$1004=B138) * ($F$5:$F$1004) );"");"")
N138 = IF( AND(L138<>""; M138<>""); L138-M138; "")

 

[RoryA]

In that case I think it's the other way round, as your formulas are looking for "727" and not 727 as a number. Is column G formatted as Text?

 

[PeteWright]

In that case I think it's the other way round, as your formulas are looking for "727" and not 727 as a number. Is column G formatted as Text?

column G is indeed formatted as Text.

 

[RoryA]

That would explain it then. If the values are currently stored as numbers, the formula will ignore them. When you f2 and enter, They are converted to text, which is what the formula is looking for.

 

[PeteWright]

That would explain it then. If the values are currently stored as numbers, the formula will ignore them. When you f2 and enter, They are converted to text, which is what the formula is looking for.

Alright, thanks again!

I've tried a different method for "importing" the data.
Instead of just pasting it from clipboard I've just used the Text import wizard... aaand... it worked!

But why?
It should't matter whether I paste the data and format column G as text or import it and set the column to text in the wizard.

Do you have an explanation?

 

[RoryA]

Changing the format doesn’t change the data type. Importing as text does.