Spreadsheet running very slow when updating certain cells

[Crinklecut]

Hi All,

I have been working on a workbook to manage the cases my office manages. The most important part of this is managing contractual timeframes and this is what the workbook is based around and so most formulas are simply IF workday functions with some edays and network days thrown in for good measure. I created a template for each contract type, with the intent that we use a shared workbook across the team, so everyone, including my manager can easily see what is occurring (I know about shared workbooks and all the deficiencies and have designed the spreadsheet around these, as frustrating as they were). User's will copy the contract type from the template tab(which is protected) The way it should work is that team members take a template from the template tab, paste it into the current tab for working and move to completed when done.

The problem with the workbook as being tested by members of the team is that specific areas of one of the contracts causes a huge slowdown in excel. The spreadsheet effectively freezes for up to 2 minutes. I humbly submit the workbook for your investigation.

The issue generally appears when updating the Done date and Ext days cells (AB111 and AB112) under the current tab under the heading SLIP AR ext, and when updating IF ext how many days under the heading SLIP Dec Ext Sent (AV111) under the current tab. This slowdown occurs whenever these sections of the refused contract type is updated no matter where it sits in the spreadsheet.

I have tried everything to speed it up including removing all conditional formatting, changing my formulas to remove redundant formulas, removing nearly all volatile formulas (there are only a couple of countif and sumif under the time taken headings for particular contracts/jobs), removing redundant names, removing unused ranges and switching to manual calculations. Nothing has made a difference to updating these cells.

The cells being updated don't even seem to be particularly important, not a lot of formulas link to them so there shouldn't be a large cascade.

I don't believe the workbook is corrupt because this behaviour only occurs on these specific cells.

Can anyone help me work out the issue and a solution for these cells so that if it crops up anywhere else I can fix it? I'm at my wits end here. Ideally the workbook should remain shared at this point (so no arrays, vb etc), though if that is the only viable solution I can make that work.

I know attaching within Mr Excel isn't allowed, I hope using a tinyupload is ok though. I can't just post formulas because I have no idea what is causing the slowdown.

TinyUpload.com - best file hosting solution, with no limits, totaly free

 

[mole999]

have you looked at Ctrl + End and see if the area you end up is as intended.

Anything that is working on complete columns i.e. over a million per column i.e. (A:A)

 

[Crinklecut]

have you looked at Ctrl + End and see if the area you end up is as intended.

Anything that is working on complete columns i.e. over a million per column i.e. (A:A)

Ctrl-end brings me to the end of the calculations as expected, no strange ranges occurring.

There are no A:A or similar ranges either, every formula looks at individual cells, or for a few specific formulas it calculates using a max range of 6 cells.

This is so frustrating as the issue is only with the formulas in those cells. The cascade caused by the formulas are quite similar, but it is such a small bounded cascade I don't see what could be occurring that makes it so low.

 

[Crinklecut]

I had thought I had found a solution by disabling background error checking, but that worked for one second, then I got excited and it stopped working for no apparent reason.

I changed to a macro-enabled workbook and tried the code and macro contained here https://msdn.microsoft.com/en-us/library/office/ff700515(v=office.14).aspx to see if I could better understand the slowdown but nothing worked. All of the timers would say 0 seconds, and then the spreadsheet would still slow down for another 2 minutes after each run.

This is incredibly frustrating. 4 cells are causing 2 minutes of slowdown whenever they are updated.

 

[StephenCrump]

Anything that is working on complete columns i.e. over a million per column i.e. (A:A)

Spot on!

Crinklecut, you have:

PublicHolidays: =Calculations!$H:$H

Shrink this down to the size you actually need and your workbook updates instantly for me.

 

[Crinklecut]

Spot on!

Crinklecut, you have:

PublicHolidays: =Calculations!$H:$H

Shrink this down to the size you actually need and your workbook updates instantly for me.

Thank you thank you thank you ! I now remember that I noticed that named range issue while I was trying a bunch of other things. I changed both that named range and disable the error check at the same time, but for some reason then only removed the error checking in the production sheet, and then forgot the named range issue and couldn't work out why everything was still slow in production.

It's amazing that the issue only appeared when those few cells were updated, it must have been a result of them being pinch point with quite a lot of precedents and dependants. Because it was only those cells I thought for sure it couldn't be an issue with any of the named ranges.

It just goes to show how important good practice is even in simple sheets, especially as they become more complex.

Once again thank you so much.

 

[StephenCrump]

You're welcome. Glad we could help!