Freezing sheet - the difference an "=" sign makes and Trace Dependents

[ciderhead]

Hello,

I am new here and use excel almost all day every day and have an issue I'd like help with if anyone has experienced this!


Firstly, I have a biggish file that runs my whole area and it contains lots of looksup and pivots etc but all works fine except for one particular sheet where I do most of my work on.

The problem I have is that when I enter a value or change a value in the sheet, the workbook freezes for up to 5 seconds.
However:

• If I copy and paste a cell value into another cell, it does not freeze.
• If I add an "=" before entering the value, it does not freeze.
• If I amend a cell in any other sheet, it does not freeze.
• If I amend a cell in this problematic sheet outside of the work zone, it does not freeze (eg. way over to the right where the cells are blank)
• If I amend a precedent cell in another sheet that feeds this problem sheet, it does not freeze (sort of ruling out dependents of the problem sheet?)

My workaround has been to just add the = before every value I add or change but as I'm in it all day, it gets very frustrating when I forget or am just in a hurry!

I've tried, changing calculations to manual but it hasn't worked and don't really want this as I want calculations updated. I've no issue with refreshing pivots manually as this is fine.

What I've now tried is Trace Dependents to see what other cells are calculating each time I change a value and it's very simple. Just another cell in same sheet that sums up a range but the weird part is that is seems to link to random cells in other sheets with a broken line. When I click the broken line to find these cells, they are not related at all and their formulae don't calculate from this cell. So that's why I am here !

Now that I've typed this, I don't think my issue is related to the dependents as that would not explain why putting the "=" sign before a value solves it.

Just to add also, it's not a cell formatting issues as I've tried changing from number to general etc.

Just can't explain it!

Thanks for reading and hope someone has experienced this and found an easy solution!!

thanks

[Glenn Kennedy]

I think thatwe'd really need to see it (suitably anonymised - if needed) to be able to help.

[Special-K]

If you have lots of array formulas they'll slow the sheet down.
If you have lots of INDIRECT() formulas they'll slow it down too.

[romperstomper]

Is there any code behind that sheet, especially a Worksheet_Change event?

[ciderhead]

Hi,

There's no VB code or anything too fancy. I just find it really strange the adding "500" to a cell can freeze it but adding "=500" to the same cell doesn't freeze it. Everything else dependant on that cell or lookedup is probably irrelevant when I think about it, if the "=" makes a difference? That doesn't change dependant or precedent cells....

For the life of me, I just don't know. All I know is that the file is too important and advance to scrap and start again so I am determined to figure out a solution. I'm pretty sure it's nothing to do with size or complexity of arrays if a simple "=" can fix it. Must be a setting or a format of some kind?

[romperstomper]

Does the same thing happen if you start Excel in Safe Mode, by holding down the Ctrl key while starting Excel and answering Yes to the prompt, before opening the workbook?

[ciderhead]

Hi- yes starting in safe mode makes no difference to this problem.

[romperstomper]

Then I think, as Glenn said, we'd need to see the workbook (with anonymised data, but formulas intact).