File freezes temporarily

I have a file in which I have been working for over 2 years. Since a week or two the file suddenly freezes when adding records to the first sheet. After some time (on average between 5 and 10 minutes) it suddenly unfreezes again and my last entry was saved correctly.
I can not find a cause.... maybe someone can shead a light on it?

The file contains 10 sheets.
Fase-1 artikelbestand
Magento invoerblad
Import_Beschrijvingen
Meta Titels
Descriptions Serie tekst
Marketingtekst
Productcategoriën
Beschrijvingen_precalculaties
Referenties
Kolomnummers

Fase-1 artikelbestand:
On this sheet a user enters new data in columns A-M, the subsequent columns are then populated by formulas.
This sheet has remained pretty much unchanged for a long time so I doubt the issue is here.
However, the freeze occurs whenever a user enters new data in column A.

Magento invoerblad:
This sheet uses formula's to combine data from several sheets: Fase-1 artikelbestand, Meta Titels, Descriptions Serie tekst, Marketingtekst and Productcategoriën.
There have been recent changes to the formulas on this sheet. Mainly in the rows with long descriptions and Meta Descriptions

Beschrijvingen_precalculaties:
This sheet only uses formula's to transpose data from Magento invoerblad:

Import_Beschrijvingen:
This sheet is fairly new. I have designed it and it's "helpers", the sheets: Beschrijvingen_precalculaties and Referenties, to create a Magento 2 Import file for descriptions and meta's.
The formula's here use data from the sheets, Beschrijvingen_precalculaties and Referenties.

So, if you've understood my story so far you will see that there is sort of a train. A user enters data in Fase-1 artikelbestand. That data is processed by formula's on Magento invoerblad: and that data is then processed by formula's on Import_Beschrijvingen:. Somewhere in the train is something that causes the spreadsheet to freeze up, when a user enters new data.

Can anyone tell me where the train malfunctions?

I also posted this question on MrExcel. Please find it here: https://www.mrexcel.com/board/thread.../#post-5784387

Nothing in your description really helps (sorry). It's like showing us a picture of the outside of your car when what we need to look at is the engine.

What's in the file?

1. How many rows of data on each sheet?
2. What formulae do you have? Do they contain volatile functions (e.g. INDIRECT)?
3. Any VBA code?
4. Do any of your formulae use full column references (e.g. A:A)?
5. Any PowerQuery or PivotTables?

Excel isn't really designed to be a database, so it can get unwieldy when things get too big or complex.

EDIT: Just seen that you have added an attachment!

Hi Ali,

You were too quick! Haha, but I like that! It was a bit unclear to me how to attach an attachment. I was trying to use the attachment button on the WYSIWIG editor menu, which doesn't do much. Then I saw the manage attachments link below the thread.... I hope you can find my issue.

I'm sorry - I've had a quick look, but don't have time at the moment to spend trawling through to work out what the issue is. I presume you have checked that the sample file does display the problem?

Hopefully someone else will have the time to give you.

Try to paste the following data into the file and see what happens (it is data for only 10 new records)

I pasted your data and "nothing" happened i.e it did not freeze.

you could eliminate the many INDIRECT which do cause performance problems.

e,g in C4 of "Magento invoerblad"

=IF(INDEX('Fase-1 artikelbestand'!$B2:$B100,COLUMNS($A:A))="","",INDEX('Fase-1 artikelbestand'!$B2:$B100,COLUMNS($A:A)))

Administrative Note:

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until the link is provided.)

Hi Ali,

I have added a link to the post in the other thread.
I also requested a link back to this forum from the other thread, but I have to wait for the moderators there, because I can not edit my own posts there, yet.

So hopefully support with my thread here can be resumed.
I have tested the alternative formula from @JohnTopley in my file and it seems to be working.
Now I only need to understand the formula a bit better.... so I can change all formulas with INDIRECT to this one.
I would very much appreciate a bit of clarification and another example with a more complicated formula...
for instance: in C5 of "Magento invoerblad"
I suspect, that I can process the rest if I know how to do that one.

Thanks in advance,

Tijs

in C5

Replace the INDIRECT

INDIRECT("'Fase-1 artikelbestand'!$F"&C$2+1)

with

INDEX('Fase-1 artikelbestand'!$F2:$F100,COLUMNS($A:A))


repeat for other ranges [as per post #7]

I got it, thanks.... already modified rows 4, 5, and 6. I think I will manage to do the rest aswell.
Thanks a lot.... Let's see if, once I'm finished, the performance is better.

Hello @JohnTopley,

My file doesn't freeze as much anymore as it did, so there has been a significant improvement. There are no more indirect-formulas in the entire workbook. However, I still get freezes every now and then and I suspect they originate from the sheet called "Import_Beschrijvingen". That sheet uses vlookups to transpose data from the sheet "Magento invoerblad". I think it's these vlookups that are responsible for the freezes. They are all connected (ultimately) to the skus in column A of the sheet "Fase-1 artikelbestand" and if I modify a sku there, it tends to still freeze up for a while.

Can you help me out again with an example to modify the formulas on "Import_Beschrijvingen" so that they perform the same task, but without vlookups?
I think it might also be done with using INDEX, but I am unsure how.

Thanks in advance.

First recommendation is to change the sheet "Referenties" as per attached. And look at INDEX match.

And can you post an updated file (without the Indirects).

I will then look at possible improvements.

Hi @JohnTopley,

All the indirects are out now and I have modified the Referenties-sheet according to your suggestions.
These changes have gotten rid of some of the freezes I was getting, but I still get temporary freeze whenever I do one of the following:

• Edit a sku field on the sheet: Fase-1 artikelbestand
• Cut and paste cell content to a different cell (anywhere in the workbook)

I can avoid there freezes by:

• making sure I first delete content from a sku column and paste in new content as values
• copy content from a cell (or more cells) and then paste as values in the destination cells. After that delete the content from the original cells.

This is however prone to accidental freezes because you forget to handle that way and accidentally cut and paste something... so I hope you can find the cause of these freezes and eliminate it from my workbook.
Attached is the new file

Edit a sku field on the sheet: Fase-1 artikelbestand

If I ADD a SKU (copy from A2 to A3) there is no obvious freeze [but initial load time is noticeable].

So sorry but I have no idea why it freezes

FYI that I've downloaded the last version Pluginguin attached, and repeated John Topely's last action (copy from A2 to A3). Definite freeze, it's been about three minutes so far and still counting.

This is my first look at this wb, so no answers yet, but I'm looking at it, too.

EDIT: Finally had to quit Excel. Redownloaded the workbook, repeated actions, no pause, everything works fine. Scratching my head.

Hi all,

I decided to recreate all sheets into a google spreadsheet and everything is working smoothly in there.
Still don't know what went wrong in my original excel file, but I'm out of trouble.
Thanks for all your help.