Suddenly my file starts freezing up when adding new data

pluginguin

New Member
Joined
Aug 10, 2016
Messages
21
Office Version
  1. 2019
Platform
  1. Windows
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?
The file in question can be downloaded from the following location:
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Just from a design perspective. Indirect is a volatile function and should be used sparingly. Your file has a huge number of indirect formulas.
It doesn't look like you even needed them eg
this
Excel Formula:
=INDIRECT("'Fase-1 artikelbestand'!$N"&E$2+1)
Could easily be handled with this:
Excel Formula:
=INDEX('Fase-1 artikelbestand'!$N$1:$N$50,E$2+1,0)
(just makes sure the the N50 is more rows than you think you will ever use.)
 
Upvote 0
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: File freezes temporarily
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,708
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top