Cells not updating

[Guest]

I have built a very large and complex financial model (around 5mb file size)
and am having problems with cells not updating. This has nothing to do with
iterations being switched on/off or with circularity (i have tested for this
- calcs are set to auto and I have hit F9 a million times when i switch to
iterations, also there is no circularity). It only seems to happen once the
file becomes very large or the model particularly complex (this one is 50
work sheets with average 80 cols and 100 rows)

When I save the file as a new version, the cells all update. Also, when I
re-copy the formulas, they update. It is very very bizzare... Has anyone else
had this problem and if so what did you do to fix it?

 

[Pete_UK]

Seems like the calculation mode has been set to manual - click on
Tools | Options | Calculation tab and ensure that automatic is
checked. Save the file to ensure the settings are fixed (although if
you open a file which is set to manual and then open another file in
the same XL session, the second one will inherit the manual settings).

Hope this helps.

Pete

 

[Guest]

As i already said, this is not an issue with the calculation settings. I have
already thoroughly tested this. Has anyone else had a similar problem?

 

[Francois via OfficeKB.com]

I have built a very large and complex financial model (around 5mb file size)
and am having problems with cells not updating. This has nothing to do with
iterations being switched on/off or with circularity (i have tested for this
- calcs are set to auto and I have hit F9 a million times when i switch to
iterations, also there is no circularity). It only seems to happen once the
file becomes very large or the model particularly complex (this one is 50
work sheets with average 80 cols and 100 rows)

When I save the file as a new version, the cells all update. Also, when I
re-copy the formulas, they update. It is very very bizzare... Has anyone else
had this problem and if so what did you do to fix it?

Your file may seem very large to you, but to excel it isn't.

Assuming you have a reasonable spec PC, I can see no reason why it wont
calculate.

Sorry I can't offer any help.

 

[Dave Peterson]

I've never seen this, but a few people have complained that excel doesn't recalc
correctly even when calculation is set to automatic.

This may work for you (it's worked for those other people).
Select all the sheets
Select all the cells
Edit|replace
what: = (an equal sign)
with: = (that same equal sign)
replace all

Ungroup the sheets!!!

And test it out.

Changing the = to = forces excel to reevalate each formula and can wake it up.

 

[Niek Otten]

Look here:

http://xldynamic.com/source/xld.xlFAQ0024.html

Especially the part about a corrupted dependency tree

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| I have built a very large and complex financial model (around 5mb file size)
| and am having problems with cells not updating. This has nothing to do with
| iterations being switched on/off or with circularity (i have tested for this
| - calcs are set to auto and I have hit F9 a million times when i switch to
| iterations, also there is no circularity). It only seems to happen once the
| file becomes very large or the model particularly complex (this one is 50
| work sheets with average 80 cols and 100 rows)
|
| When I save the file as a new version, the cells all update. Also, when I
| re-copy the formulas, they update. It is very very bizzare... Has anyone else
| had this problem and if so what did you do to fix it?

 

[Guest]

Thanks - that is exactly the answer I was looking for.

thanks alot guys.

 

[Guest]

thanks - that's fixed it. another poster suggested pressing ctr+alt+shift+F9,
this also works a treat.