HELP: big problem with Excel, source cells, crashing and general madness

[Tristán White]

I have been on microsoft.public.excel.crashesgpfs but so far just the
one reply (from someone who has exactly the same problem as we do and
has not yet solved it either).

Anyone here can help???

My boss has this massive Excel spreadsheet with a number of worksheets
linked with other spreadsheets etc.

He is using Excel 2000 on Office 2000.

His computer was crashing with the above whenever he tried to do work
on it. We bought him a brand new computer, 1 gig of RAM, 80 GB of HDD.
We're a small company - a charity - so this was as powerful as we
could afford.

This was a week ago and it's happening again.

We desperately need to get this spreadsheet working, and he cannot
simplify it (or rather, that would take too long). I have changed the
Virtual Memory settings of the PC and it made no difference.

Having done some googling around it seems that this is because Excel
2000 crashes with large or complex spreadsheets - According to
Knowledge Base report http://support.microsoft.com/?kbid=313275

In versions of Excel earlier than Microsoft Excel 2002,
the memory limit is 64 MB. In Excel 2002, the limit is
increased to 128 MB. In Microsoft Office Excel 2003,
the limit is increased to 1gigabyte (GB).
Because this is a per-instance limit, this problem may
occur if you have two or three large workbooks open, or
one very large workbook. If you are working with several
workbooks, try to open them in separate instances of Excel.

We've tried everything they suggest apart from upgrading to Excel
2003.

We're going to buy Excel 2003 this morning and install it onto his
machine into Office 2000 - or we may even go the whole hog and get
Office 2003 as well, we'll see.

We were hoping that this would 100% SOLVE THE ISSUE?????

Microsoft seem to suggest it will, but "upgrade your software" is
pretty much their solution to everything (for example, I still use XP
service pack 1, and whenever I have a small computer problem and ask
the computer to find out why, it always says that upgrading to SP2
will solve the problem, which is rubbish as one of the computers
across the room has SP2 and crashes with the same document!)

But now the bombshell. A guy on the other newsgroup has the same
problem, upgraded and this didn't help!! He writes, and I quote:

I don't think upgrading will solve your problem. I'm facing the same problem
with some linked spreadsheets. In the office we work with Excel 2002. I've
tried it at home where I have Excel 2003 and a pc with 1024 Mb memory. I
still get the same error.
I also found the article in the knowledge base and if you read it carefully
you will see that not only the amount of memory that Excel can handle is
limited, but also the number of source cells is limited to 32760. As far as I
can tell (and I am absolutely no pro) is this our problem. You can read that
Microsoft fixed the amount of memory problem but the article does not mention
any increase of the number of source cells that Excel can handle.
For us it's also very important to get the sheets working asap, but I still
don't know how.

So I guess if anyone has the answer to this question it would help him
and it will help me if the upgrade doesn't work.

Limiting source cells in a huge document? errrm is that a very
timeconsuming complex job? or is there maybe an application that can
help with it?

I guess the first question would be: will upgrading to 2003 increase
the number of source cells (as well as the memory that Excel can
handle).

Perhaps someone has written something in open source that can increase
the number of source cells Excel handles?? I don't know!! There must
be an answer to this obviously common problem!

THANK YOU!!!

TRISTÁN

 

[Bill Sharpe]

You say the spreadsheet is "massive." Just how big is it? Is it all one
sheet or a workbook with multiple sheets?

Excel 2003 may or may not help, but you really need to consider
simplifying the file. "Taking too long", whatever that means, to fix it
is better than having the computer keep crashing.

I assume you have looked at the usual suspects like rouge entries in
high-numbered rows and/or columns, copying and pasting to a new
worksheet, etc.

Bill

I have been on microsoft.public.excel.crashesgpfs but so far just the
one reply (from someone who has exactly the same problem as we do and
has not yet solved it either).

Anyone here can help???

My boss has this massive Excel spreadsheet with a number of worksheets
linked with other spreadsheets etc.

He is using Excel 2000 on Office 2000.

His computer was crashing with the above whenever he tried to do work
on it. We bought him a brand new computer, 1 gig of RAM, 80 GB of HDD.
We're a small company - a charity - so this was as powerful as we
could afford.

This was a week ago and it's happening again.

We desperately need to get this spreadsheet working, and he cannot
simplify it (or rather, that would take too long). I have changed the
Virtual Memory settings of the PC and it made no difference.

Having done some googling around it seems that this is because Excel
2000 crashes with large or complex spreadsheets - According to
Knowledge Base report http://support.microsoft.com/?kbid=313275

In versions of Excel earlier than Microsoft Excel 2002,
the memory limit is 64 MB. In Excel 2002, the limit is
increased to 128 MB. In Microsoft Office Excel 2003,
the limit is increased to 1gigabyte (GB).
Because this is a per-instance limit, this problem may
occur if you have two or three large workbooks open, or
one very large workbook. If you are working with several
workbooks, try to open them in separate instances of Excel.

We've tried everything they suggest apart from upgrading to Excel
2003.

We're going to buy Excel 2003 this morning and install it onto his
machine into Office 2000 - or we may even go the whole hog and get
Office 2003 as well, we'll see.

We were hoping that this would 100% SOLVE THE ISSUE?????

Microsoft seem to suggest it will, but "upgrade your software" is
pretty much their solution to everything (for example, I still use XP
service pack 1, and whenever I have a small computer problem and ask
the computer to find out why, it always says that upgrading to SP2
will solve the problem, which is rubbish as one of the computers
across the room has SP2 and crashes with the same document!)

But now the bombshell. A guy on the other newsgroup has the same
problem, upgraded and this didn't help!! He writes, and I quote:

I don't think upgrading will solve your problem. I'm facing the same
problem
with some linked spreadsheets. In the office we work with Excel 2002.
I've
tried it at home where I have Excel 2003 and a pc with 1024 Mb memory.
I
still get the same error.
I also found the article in the knowledge base and if you read it
carefully
you will see that not only the amount of memory that Excel can handle
is
limited, but also the number of source cells is limited to 32760. As
far as I
can tell (and I am absolutely no pro) is this our problem. You can read
that
Microsoft fixed the amount of memory problem but the article does not
mention
any increase of the number of source cells that Excel can handle.
For us it's also very important to get the sheets working asap, but I
still
don't know how.

So I guess if anyone has the answer to this question it would help him
and it will help me if the upgrade doesn't work.

Limiting source cells in a huge document? errrm is that a very
timeconsuming complex job? or is there maybe an application that can
help with it?

I guess the first question would be: will upgrading to 2003 increase
the number of source cells (as well as the memory that Excel can
handle).

Perhaps someone has written something in open source that can increase
the number of source cells Excel handles?? I don't know!! There must
be an answer to this obviously common problem!

THANK YOU!!!

TRISTÁN

 

[David McRitchie]

Hi Tristán,
Have you checked your lastcell Ctrl+End
http://www.mvps.org/dmcritchie/excel/makelast.htm

What does worksheets linked to other spreadsheets mean?
are they linked to worksheets in the same workbook,
or to other workbooks? Could you include a typical
example of such a link, are all involved workbooks open.

How large (bytes) are your workbook(s).
Do you have a lot of hyperlinks, if so object or HYPERLINK worksheet formula.
Do you have a lot of shapes, buttons, comment boxes, text boxes

You certainly have a lot of RAM (1024MB)

 

[Harlan Grove]

Tristán White wrote...
....

My boss has this massive Excel spreadsheet with a number of worksheets
linked with other spreadsheets etc.

He is using Excel 2000 on Office 2000. ....
This was a week ago and it's happening again.

We desperately need to get this spreadsheet working, and he cannot
simplify it (or rather, that would take too long). I have changed the
Virtual Memory settings of the PC and it made no difference.

....

All large spreadsheets are as stable as building skyscrapers by
stacking RVs on top of each other and connecting them with bungee
cords. Huge spreadsheets with lots of external links are inherrently
unstable, and if there are a lot of array formulas and/or lookups on
large ranges, you're begging for trouble.

Like it or not, simplifying the workbook is the only option with a real
chance of success. Wholesale upgrading isn't likely to fix the problem.
Break the problem that this huge workbook is supposed to solve into
pieces, and use a separate workbook for each piece, and limit the data
that needs to flow from one to the next.

 

[Tristán White]

Like it or not, simplifying the workbook is the only option with a real
chance of success. Wholesale upgrading isn't likely to fix the problem.

So far so good. Upgraded to Office 2003 Excel 2003 and the document is
no longer crashing.

fingers crossed.