Cells move themselves?

[Igor]

I have an Excel file loaded with complicated functions. On one sheet there is
a set of different tables serving as a source for calculations on the other
sheet. Functions used are mostly logical ones and functions of selecting
value of specific cells out of the source tables based on complicated
criteria.
From time to time with no obvious reasons data and formats of some cells on
the source sheet move to a different location (always on the same sheet,
usually few cells move to the left, different cells to different distance,
always on free space “squeezing†between data tables) while references to
these cells still indicate their original location.
I’m pretty experienced Excel user and I’ve never created files loaded with
so many and so complicated formulas before. So my guess is that Excel lacks
resources to handle this number of calculations. If that is true, does
anybody have an idea how to “reinforce†Excel capabilities? Note that the
calculations cannot be simplified except for splitting long formulas between
few cells.
Thank you for advice in advance.

 

[Nick Hodge]

Igor

Never heard of them moving. Calculation was a little flaky across 3-D
references in XL97/2000 but with service packs and the 'Full Calc'
(Ctrl+Shift+F9) this is normally not an issue.

It's possible Excel is struggling to resolve the formulas (I've seen
worksheets taking 20 minutes to calculate), but I've never seen it 'move'
formats and formulae

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Igor]

Nick,
Thank you very much for your reply.
There’s one other thing: when I type the formulae into cells in that file,
names of the cells referred to are always colored, but as soon as the formula
gets longer (probably over five hundred characters) coloring disappear.
That’s another reason why I suspected lack of resources but looks it’s not an
issue.

 

[Nick Hodge]

Igor

You could check out Excel limits in help. This gives you some maximums for
length of formulae, etc

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)