Excel keeps calculating - no idea why?

Hi all


I have an issue that Excel keeps calculating for a long time.
Not that long, like 15 seconds, but way to long to work fluently with this file.
Also clicking cells and pushing enter after I filled in something seems to go slow.

I really tried to remove lots of things and change formulas and so on but nothing helps.
I'm also working with a similar file already so lots of formulas are based on that file and there it's working perfectly.

Pic 1: Planning where you can fill in an article number and data should appear immediately, the second error is for the amount.
Pic 2: This appears on the production tab.
Pic 3: Tab 'Deliveries in' if somethin' gets delivered (fill in manually).
Pic 4: Result of those tabs together to follow up stocks.

Excel file as well as an attachment.

If somebody would be able to help me I would be really grateful.

Thanks a lot already.

In you wk01 sheet you have lots of array formulas in col M and N. Formulas like =IFERROR(INDEX(ARTICLES!$D$9:$D$88;MATCH(I6;ARTICLES!$A$9:$A$88;0);0);"") do not need to be array entered

Originally Posted by bebo021999

Try to delete 90% of indirect function in that sheet (may be, from column F to end right columns) to see if it is faster.

This doesn't help.

Originally Posted by Pepe Le Mokko

In you wk01 sheet you have lots of array formulas in col M and N. Formulas like =IFERROR(INDEX(ARTICLES!$D$9:$D$88;MATCH(I6;ARTICLES!$A$9:$A$88;0);0);"") do not need to be array entered

Can you explain a little more or make an example formula?
As English is not my native language I'm a bit struggling what you're meaning with array entered.

I changed ALL the formulae in "Production" to fixed (i.e. not INDIRECT) formulae and then simply copied the sheet. There was no delay.

Doing the same exercise with the "Production" INDIRECT formula: the delay was significant as Excel recalculated.

So my conclusion is that the prime cause of the performance problems IS the use of INDIRECT.

Originally Posted by kvsrinivasamurthy

In the production sheet are you often changing the values in column B . If it is often not changed ( once in a way ok) we can use work sheet event to put formulas in the range, where you are now using iINDIRECT formulas , without INDIRECT function. It avoids delay . if values in Col B is changed worksheet event will change required formulas automatically.
If you are ready to experiment on a copied file I will give code.

Can you change in file and upload? Thanks!

Other mentioned tips didn't solve the issue unfortunately....

Originally Posted by JohnTopley

I changed ALL the formulae in "Production" to fixed (i.e. not INDIRECT) formulae and then simply copied the sheet. There was no delay.

Doing the same exercise with the "Production" INDIRECT formula: the delay was significant as Excel recalculated.

So my conclusion is that the prime cause of the performance problems IS the use of INDIRECT.

Yes, you're right. If you remove ALL indirect it's solved (in the past I always removed most of the formula but never in ALL cells). The weird thing is that this function was already included in my previous file. So I must have changed something, will check again.
If you see the issue in it, please inform.

Thanks a lot, that's already one step further.

Have you even tried my suggestion in post #8 ?