File Size - Calculation
 

I have a workbook on manual calc so I can finish some minor changes to
a model that was at 100MB but I was able to bring down to 60MB. With
the few changes I made it should be around 40MB but it isn't saving.
I've shut down the computer a few times and tried saving it with only a
couple minor changes but everything I try gets 'non-responsive' for at
least 20+ minutes. This model only has 2 sheets and the data tab has
28,000 rows with 78 columns. The summary tab is made up of 'SUMIF'
statements and has 3 small tables. Has anyone run across a way to
overcome this situation?

You might try to force an absolute recalculation of the spreadsheet by
simultaneously pressing CTRL-ALT-F9.

In the latest version of Excel you can rebuild the entire recalculation
tree press CTRL-ALT-SHIFT-F9. This should be necessary only once (if
you save the rebuilt sheet).

That might get you past the non-responsive issue. Good luck with it!

40MB seems to be like a very large file to me.

But instead of using =sumif()'s, maybe a pivottable would work better.

To read more about the pivottable stuff, you may want to look at some links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

wrote:

I have a workbook on manual calc so I can finish some minor changes to
a model that was at 100MB but I was able to bring down to 60MB. With
the few changes I made it should be around 40MB but it isn't saving.
I've shut down the computer a few times and tried saving it with only a
couple minor changes but everything I try gets 'non-responsive' for at
least 20+ minutes. This model only has 2 sheets and the data tab has
28,000 rows with 78 columns. The summary tab is made up of 'SUMIF'
statements and has 3 small tables. Has anyone run across a way to
overcome this situation?

--

Dave Peterson

I take it your SUMIF is on each of your 28K rows, what does it look like.

When you save your workbook, it is recalculated.

---
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

wrote in message oups.com...
I have a workbook on manual calc so I can finish some minor changes to
a model that was at 100MB but I was able to bring down to 60MB. With
the few changes I made it should be around 40MB but it isn't saving.
I've shut down the computer a few times and tried saving it with only a
couple minor changes but everything I try gets 'non-responsive' for at
least 20+ minutes. This model only has 2 sheets and the data tab has
28,000 rows with 78 columns. The summary tab is made up of 'SUMIF'
statements and has 3 small tables. Has anyone run across a way to
overcome this situation?