Slow updates

[Europaul]

I have a spreadsheet with 3 tabs. I input data into 1 and it populates the other 2...
Only 7 items get populated to one tab and 3 to the other, yet it takes over 30 secs for the 2 tabs to update - this was never so slow in Win 7 & previous Excel...
Any ideas please?

[#2]

For me, your file works without problems, quickly and does not hang anywhere.
Here is my suggested solution ...
File> Options> Advanced> Formulas> Use all processors on the computer: (click the radio button)

So it should actually be faster ... if it wasn't clicked

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.

[#3]

Thanks Nikolino,

I already had that ticked... no improvement

BR,

P

[#4]

Try this too:
Formulas> Name Manager
Delete all referenceless links

= #REFERENCE! #REFERENCE!

Especially the #REFERENCE! errors with links.

I hope that I could help you with this information / links.

[#5 · Accepted solution]

@Europaul 

An issue is in formula in cell N134 of Logbook

=SUMIF(A:A,">"&TODAY()-28*(E:E))

Here for each cell in column A you use 1 million criteria (all cells in column E) to sum, thus formula makes 1M*1M = 1e12 calculations. After that it tries to return 1M results, but have not enough space for it and returns #SPILL error.

In previous versions of Excel silent implicit intersection mechanism worked in the background,  formula calculated only for the criteria from current row.

Not sure what exactly you'd like to calculate, simply disabled the formula. Please check attached file if it is updated correctly.

[#6]

Excellent! Thank you Sergei (spasibo!) Yes, that (A:A, E:E) fixed the slow response...
Now I am curious... why did Nikolino report "For me, your file works without problems, quickly and does not hang anywhere." ???
Best regards,
Paul

[#7]

@Europaul 

Paul, you are welcome. Perhaps Nikolino is on Excel which doesn't support dynamic arrays (e.g. Excel 2019). If so mentioned silent implicit intersection works and it shall be no such issue. Such behavior helps sometimes, but in general could be source of other errors if use it incorrectly, plus dynamic arrays provide lot of new possibilities to build more reliable Excel projects in shorter time.

[#8]

Now that you write it down, Mr. Baklan, I can see what's going on with the arrays, thank you ... You are right, at the moment it is only available if you take part in Microsoft's so-called Office Insider Program.

I have to slowly decide on a direction ... subscription, or stay on 2013/2016/2019 and wait for 2022, or online?
... well, drink tea and wait :).

[#9]

@NikolinoDE , dynamic arrays are in production for 365, shall be available on all channels. That's LAMBDA only for Insider Beta.

One time purchased 2021 shall be available this fall, don't know which functionality will be included.

[#10]

It would be best if you could buy them all and use them all on one PC, depending on what you need.
Until a clear path has crystallized, some time will pass.
I will wait in advance for the 2021 and in the meantime experiment with online ... I can load that on the PC with my existing Excel.

Interesting things will come our way ... it's nice to live in this time.

Thanks for the summary - info

[#11]

@NikolinoDE 

That's limited only by amount of money you are ready to spend. You may use virtual machine with branches and install as many versions as you wish, using main version of Office on main OS. 

[#12]

Something like that is sure to come, wait for the first quantum computer ... then I'll step on the gas :)))

I wish all of us have a good day