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

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
0
5/26/2005 10:23:34 AM
excel 39879 articles. 2 followers. Follow

4 Replies
589 Views

Similar Articles

[PageSpeed] 49

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

"Trist�n White" <Tristan_White@rocketmail.com> wrote in message 
news:a98b91ln4iamf223bigb852r100cjt8tm2@4ax.com...
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 


0
bsharpe (111)
5/26/2005 4:57:02 PM
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)
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"Trist�n White" <Tristan_White@rocketmail.com> wrote in message news:a98b91ln4iamf223bigb852r100cjt8tm2@4ax.com...
> 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). [clipped]


0
dmcritchie (2586)
5/26/2005 5:46:54 PM
Trist=E1n White wrote...
..=2E.
>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.
..=2E.
>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.
..=2E.

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.

0
hrlngrv (1990)
5/26/2005 9:19:19 PM
On 26 May 2005 14:19:19 -0700, "Harlan Grove" <hrlngrv@aol.com> wrote:
<SNIP>
>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. :-)
0
6/6/2005 4:30:57 PM
Reply:

Similar Artilces:

sumifs help
I have the following formula. =SUMIFS(Table1[2],$A$11:$A$22,$A38,$B$11:$B$22,$B38) It now needs to be changed to a formula that can handle text instead of numbers. How do i do it? Use Countif instead of Sumif from =SUMIF(Table1[2],$A$11:$A$22,$A38,$B$11:$B$22,$B38) to =CountIF(Table1[2],$A$11:$A$22,$A38,$B$11:$B$22,$B38) Do you really have a function Countifs with an "S" at the end? thie maybe an UDF that needs to be modified. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this th...

Help !
I need to create a data input screen on excel where multiple users at the same time will use them & input data. This data then needs to be stored as a database as well, where i can use it to understand trends Thank you. and the question is ...? <abrahamsaj@gmail.com> wrote in message news:1132155054.927936.191640@z14g2000cwz.googlegroups.com... >I need to create a data input screen on excel where multiple users > at the same time will use them & input data. > This data then needs to be stored as a database as well, where i can > use it to understand trends >...

Displaying cell data in a chart
Previous posts indicate a successfuly method for displaying data/text from a cell into a chart. Following this procedure, I select the chart, press the equal sign, and select the cell, and then enter. The address of the sheet and the cell address are displayed after the equal sign. When I press enter nothing seems to happen. The data are supposed to display in the middle of the chart, but I do not get any display of the data. I am in a Pivot Table worksheet but did not directly insert a Pivot Chart. I used getpivotdata and created new cells for plotting into the inserted column ch...

Excel 2007 Tables
Hi, I have turned a range of data into a Table, using the new "Insert Table" tool in Excel 2007. Great new feature!! The problem is, when I insert a new row (by tabbing from the last cell in the last row), the formatting for the cell in the first column does not get applied. All of the rest of the cells in that row maintain the formatting from the row above. All of the cells in the first column above the new row have the same formatting - there is no mixing (bold and italics, for example). Any suggestions on why this would be happening? Thanks! Hi Tammy I love to see yo...

multi-thread problem
I am not sure how to correctly write this Worker Thread example program. And I realize that this specific problem is somewhat more of a C++ issue. But I really needed to make sure that I was correctly writing this part of the program. I've also encountered another problem, but I thought it could be connected with this first problem here. However, I would hoped to ask about it later. How do you write the statement that "post" the long integer in the Controller thread to the receiving function. UINT CThreadTestDlg::Thread1(LPVOID lParam) { CThreadTestDlg* pHwnd = (...

Outlook crashes when typing recipient
Every time I try to send an email message in Outlook, it crashes as soon as I start typing in the recipient address. Does anyone know what could be causing this and how to fix it? I do not have any Add-Ins installed. Thanks. It would be helpful if you provided at least some basic information such as: 1. Outlook version 2. Mail account type 3. Information store 4. The steps used to break Outlook. I suspect this didn't just start happening on its own for no reason. -- Russ Valentine [MVP-Outlook] <yofnik@comcast.net> wrote in message news:1159655665.219713.8280@e3g2000cwe.google...

how can I get a cell to change color by date to indicate expiery t
I'm trying to get the cell color to change from green to yellow to red as a date entered in the cell gets closer to that date plus 12 months or 36 months. ie some task was completed on 1 Jan 08 and will last for 24 months so I want the cell to read green if the date is still good turn yellow a month or 2 before it expires and turn red and clear the date if expired. So if the cell is empty it should show red. But I want to be able to update the date the task was completed ie it was completed again on the 15 Dec 10 before it expired so I want the countdown to expiration to restart f...

Help with this thing
It was working in the window "Transactions >> Sales >> Transactions of Sales", but exactly were publishing a Quotation, which i wanna print, more nevertheless was shut up to me network, then I closed the window and it threw several messages to me of error, and from that then it was blocked the quotation that was working. My question is as I can unblock this document? ...

Excel Macro to delete rows
Hello, I have an excel Sheet in which I have a blank row below the cell which contains "<<<Mem>>>". I vave some random data and multiple such cells. Can I get some help with the excel macros for the same? Harsh Assuming "Mem" is in column E: Sub DeleteRowBelowMem() For i=cells(rows.count,5).end(xlup).row to 1 step -1 if cells(i,5).value="Mem" then rows(i+1).Delete Next End Sub Bob Umlas Excel MVP HTH "Harsh" <cooldreamz83@yahoo.com> wrote in message news:1159983014.172239.234480@h48g2000cwc.googlegroups.com... ...

Excel chart to Access report
Apologies if this isn't the right forum, but I'm clutching at straws here.... I have a chart which must be created in Excel because the Access chart interface does not (to my knowledge) permit custom error bars. The problem is that I want then to include the graph in an Access report. I can see how, via automation, I can address the chart from code in the Access report. I just don't know how to actually include it, if you get my meaning. I thought placing an unbound OLE object control in the report would be a good start, but I don't seem to be able to move the chart data into...

Need function to insert worksheet name into a cell of that worksheet.
I have found a formula on the web help site to automatically insert a worksheet name into a cell of the worksheet. I have several worksheets and wish to identify each worksheet automatically in a cell of each worksheet. When I use the formula it initially brings in the correct worksheet name. However when I select another worksheet the prior name appears in the selected cell. Perhaps I need to change something in my formula or this is a problem that I can't correct. What is your suggestion? The following formula is what I am using in each worksheet. =MID(CELL("fil...

ole drag and drop problem in a ListView
I've implemented ole drag and drop in my application's listview. My understanding is that I should get scrolling for free but I don't get any scrolling action. Am I missing something here? I'm new to OLE so any pointers would be greatly appreciated. Regards, Omundu. ...

Help with Outlook Setup!!!
I have XP Professional installed and Office XP Professional. I have several users setup for kids, as well as my admin account. I want to setup a Limited Account in addition to the Admin account for myself for daily use. Am I just suppose to pick a different account name and login password and then setup my MSN Messenger and Outlook POP email with same username and password that I used to setup the Admin account? I don't need to share any contacts or anything, I just setup the admin email in order to setup Outlook. I want to input my contact info etc., into the Limited account and use ...

Problem copying table data as a picture onto chartsheet
I'm having a strange problem. I'm creating a special chart which has a pie chart on it as well as some tabular data that is formatted the way we want it on a worksheet, then pasted onto the chartsheet as a picture. (The pie chart aspect is working fine. I'm just having a problem with the tabular data.) When I copy the tabular data manually by selecting the data on the worksheet, then going to the Home tab and choosing the Paste menu dropdown, then "As Picture", then "Copy as picture...". In the resulting dialog box, I choose "As shown on scr...

HELP !!! I have a ARRAY Formula HELP !!!
Hello, Here is the ARRAY Formula I have and this is what I am using it for. The situation is that it worked 1 time and than not again. =INDEX(D48:K48,,MAX(IF(D48:K48<>"",COLUMN(D48:K48)))-COLUMN(D48)+1 Duty: I have a row of number that appear hourly (DOLLAR AMOUNTS), the numbe are anything from nothing to 10000. I want the hourly number to appea in specified cell. Here is an example. (I am using EXCEL 2000) Row D48:K48 answer in cell G2 1st hour D48 = $100.00 G2 Should be $100.00 2nd Hour D48 = $100.00 E48 = (nothing) G2 Should be (nothing) 3rd Hour D48 = $1...

Auto Archive problem
Hi All, We use Outlook 2003 Sp3. One of my users has this problem with the Auto Archive feature: certain folders of his mailbox don't get archived. The emails stay in these folders and others folders emails get moved to the PST file according to the Auto Archive settings. Even if I try to archive manually some of the forlers that don't work, nothing gets archived. What can I do with this? Regards, Are you aware that the Date used to archive is the Date Last Modified? This may be causing what you think is unsual behaviour. When were the items last modified? Regards Judy ...

exch 5.5 help
I am in a progress to upgrade Exchange 5.5 (on NT4) to Exhange 2k3 (on 2k3). I setup a test machine and upgrade the OS to w2k3. 1st I want to connect the 5.5 to AD, so I should install ADC. Can anyone tell me the steps? Frorestprep, domainprep, setup adc, and upgrade to exchange 2k3? If you run through the steps in the E2K3 deployment tools they will walk you through everything. -- Hope that helps. ------------------------- Jaclynn Hiranaka Enterprise Messaging Support This posting is provided "AS IS" with no warranties, and confers no rights. � 2004 Microsoft Corporation. Al...

Problems sharing a file
I'm running Excel 97 SR2 and Win98SE. When I share the workbook Excel wants to save the book. OK. It then notifies me that it contains macros & VB code ... OK. I then get an illegal operation message and it closes Excel without saving. When I open the file again, sharing is not enabled. Did I miss something? Anyone have any ideas on what is wrong? Oscar ...

Help with Formula Please 02-19-10
Need a Formula for the following: Data Table A B C D E F G H I 1 Tom A W 2 H 30 84 30 2 Peter A W 3 H 3 Nick B L 1 A 70 Columns F1:I3 from Data Table has break scores for each player. Below is the Result Table where I need to show a summary report for high breaks. I have no problem with Break as I use the Large function. I need a formula to insert in A1 and A2 to place the name for the corresponding breaks below. Result Table High Breaks A B Name Break 1 ...

Excel Needs to Close
Frequently, when turning on my computer, I will get the message something like "Excel has encountered a problem and needs to close." I may not have used Excel for days and it definitely was not open when the computer was turned off. Any clues? Do I have a problem? Ed, Look for an Excel file, or Excel itself, in your Startup folder (Start - All programs). Scan for viruses. -- Earl Kiosterud www.smokeylake.com Note: Top-posting has been the norm here. Some folks prefer bottom-posting. But if you bottom-post to a reply that's already top-posted, the thr...

Excel charts and Word docs
In Office 2003, how do I import an excel table into a word doc? In Ms Word go to Insert - Object - Create from File and select the xls file that contains your table. "Frustrated" wrote: > In Office 2003, how do I import an excel table into a word doc? Copy the range in Excel, switch to Word, and paste. Word puts the Excel data into a Word table. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "Frustrated" <Frustrated@discussions.microsoft.com> wrote in message news:90F...

OnActivateView problem
I have a class derived from CView, say CViewA. I have controls in a CDialogBar. when the view CViewA is activated I enable the button on the dialogbar and when it is deactivated I disable the buttons on the dialog bar. But i donot want the buttons to enable or disable when there is switch between same type of view. This is creating refreshing problem. First the CViewA, OnActivateView is called with bActivate equal to False, then the same function is called with bActivate equals to true. I used the following code to find which view is getting active:- CMainFrame *frame = (CMainFrame *)AfxGet...

help...help...help
I just installed Microsoft Office XP Professionaql with no problem. However, whenever I try to perform any task such as opening contacts area in order to create an entry, I receive a dialog box with Microsoft Outlook and a yellow exclamation point. Also, included in the dialog box are the words could not open the item, try again. Other information that might be important is that I use a pst file. The error message also occurs when I try to open the Contact folder from the folder list as well as when I try to perform any function. It was a clean install not an upgrade. Could you ...

Need help in data copying.
Hi I have an invoicing file in excel (Sheet1). I need to store the dat which is invoiced into another sheet. My Invoice Data starting from Ro 8 and column B to F (The first item is from B8-F8, second item i B9-F9). B-Item Code, C-Item Name, D-Qty, E-Price, F-Total. Once I print the invoice, I need to transfer the data to another shee (Sheet2) . When I create another invoice, the new data should be added below t the previous data in Sheet2. So that I can have all the items I sol in Sheet2. Can someone help me sending a macro for it??? I will be grateful to you. Thanks in advance Tom -...

Macro Help
Hi, I had alot of help yesterday from Jacob with the following macro, but am getting a 'run time error 13' when trying to run the macro, and i cannot see why. Any help much appreciated Sub OLApp() Dim objOL As Object, objApp As Object, lngRow As Long Set objOL = CreateObject("Outlook.Application") For lngRow = 9 To Cells(Rows.Count, "A").End(xlUp).Row If Range("E" & lngRow) = "" Then Set objApp = objOL.CreateItem(1) With objApp ..Subject = "Change Password for system" & Range("A" & lngRow)...