Excel File Corruption

[MSH]

I have a large excel spreadsheet (exceeding 5MB) that suddenly failed to
update cell references. Due to the amount of changes being made on this
particular day, I had put about three hours of work in ( all saved
periodically) when I realized that cells referring to other celss were not
updating when the values of the xource cells changes. I also discovered
that cell references had failed to update when rows were added above the
source cells.

I work for a large international services firm, but our in-house tech group
had no answers, excpet to suggest closing everything, re-booting, and
reopeing the file. This did not work, so a reverted to an older version of
the file which appeared to be OK. As I went through the process of
re-making the changes, I was diligent to watch for signs of the problem.
About half-way through the rebuild, the errors began to reappear.

Since this spreadsheet is supporting the decision making for a multi-million
dollar project, I need to know if there is anyway to prevent this type of
melt-down. My immeidiate solution was to completely rebuild the
spreadsheet -- painful as that was.

Thanks for any suggestions.

Mike

 

[Dave Peterson]

Are you sure you didn't have calculation set to manual?
(tools|options|calculation tab)

Did you have any user defined functions in your workbook? Do they handle errors
nicely? Sometimes when a UDF fails miserably, it can cause recalculation
problems.

(I've never seen excel do this kind of thing on its own.)

If these don't apply, you may want to post back with the version of excel you're
running.

 

[Fabian]

MSH hu kiteb:

I have a large excel spreadsheet (exceeding 5MB) that suddenly failed
to update cell references.

This could be because of the amount of memory being used. As a general
rule, if an excel file is over 1 meg, they had better be a damn good
reason for not using a more heavy-duty program for the task. It might
well be that a database application is better suited to your needs.

 

[Dave Peterson]

1 meg seems a little small to dump excel.

I work with files that are routinely 15 meg and I don't have this problem.

(When the file size gets near 20 meg (and over), I've seen excel get
sluggish--but never a problem recalculating correctly--slow, yes, but still
correct.)

 

[MSH]

The first thing I checked was recalculation. It was, and is still, set to
automatic. The only "user functions" I am aware of are two macros that will
protect and unprotect all sheets. These were from a macro set I purchased.
They have been used regularly for some time with no problems noted.

Others have suggested use of a database for such a large file, but the meat
of the file is computations, including various financial formulas. The
general concensus was that the database was good for dealing with data, not
so many formulas, especially since the structure of the "world" being
modeled was constantly changing. We did not have time to confer with a DB
expert rather than do the Excel work oursleves.

As requested, here are the specifications of my system:
Computer: Toshiba TE2000
Memory: 512MB
OS: Windows XP
Office: XP (2002 (10.4302.4219)SP-2)

Last. You note that unfriendly UDFs can cause recalculation problems.
Assuming that is the problem, once it has happened, is the affected file
toast, or is there a way to recover?

Mike

 

[Fabian]

Dave Peterson hu kiteb:

1 meg seems a little small to dump excel.

I work with files that are routinely 15 meg and I don't have this
problem.

(When the file size gets near 20 meg (and over), I've seen excel get
sluggish--but never a problem recalculating correctly--slow, yes, but
still correct.)

I suppose it depends on the specifications of the machine. For personal
use (ie on my high performance home machine), I've never *needed* excel
files over about 300kb or so. At work, Ive never had use of a machine
with more than 32 meg of memory. That tends to crimp performance.