Excel Won't Calc?

E

Eric

I sometimes find that when spreadsheets get to be a certain size they often
don't calc properly. Any idea as to why?

Thanks,
Eric
 
V

Vacation's Over

don't calc properly......what does that mean?
if auto calc is set, a well designed workbook would need to be very large to
degrade performance enough to "not work"

how large and how complex, and how well designed is your workbook?
 
E

Eric

How well designed, I can't say, but the file is 18mb and has 5 pivot tables
driving off of 2 databases and feeds several financial statements. I've had
this happen to me before, but have never been able to figure out why. It
seems to only happen in larger files. For some reason the simplest
calculations will not work unless you either click on the cell or hit F2 then
enter. I have an if statement in a database that references back to an imput
date cell. =if(c3<=date,B5*g5,0) Very simple calc, but sometimes when I
change "date" it calculates, sometimes not. I have calc on auto and have hit
F9 a hundred times to no avail. I wish I knew what I was doing wrong, as I
said I've had this happen before.

Any help is greatly appreciated,

Thanks
Eric
 
V

Vacation's Over

at 18mb Excel on a modern machine should handle it

With something that big it is hard to diagnose

first look for size problems:
You could copy each sheet into a new file, without worring about formulas
and such, compare the new file size to the old??/

check system memory issues
http://www.decisionmodels.com/index.htm

Are your databases remote or on the PC?

Are Other apps running?

If this is an "old" file that has seen a number of revisions it may need
some housekeeping:

reset used ranges
http://www.contextures.com/xlfaqApp.html#Unused

delete unused charts

if VBA present run Code Cleaner
http://www.appspro.com/Utilities/CodeCleaner.htm

File could be corrupt requiring coping each sheet to a new file.

pictures & logos could be corrupt?


AS you can see there are many sources of trouble in a large file. If you
want it fixed you many need to just use process of elimination:
delete one sheet at a time until problem is fixed then figure out what is
special about that sheet.

you should invest in PUP (saved me many times over)
30 day free trial

http://j-walk.com/ss/pup/pup6/index.htm

after download run the AUDIT WORKBOOK tool

the report it produces will map out the complexities of your file and may
lead to an answer.

You could also gain from John's books also listed at this URL
 
E

Eric

Yeah, that's the only solution I could come up with as well. Once I'm
through creating Frankenstein's Monster, copy the data into a new workbook
without the formula's and recreate. I was just curious to know if their is
a way to prevent the problem.

Thanks for you insight,
Eric
 
V

Vacation's Over

NO! I was not saying to recreate everything!

Just do a quick copy over and then compare file sizes.
If there is a big diference then You may have a "garbage" issue.

For instance all those charts you make then don't use and delete them.....
They may still be in the file.

only if there is a big size differnce would I start from "scratch"

do some of the diagnostics i posted you may get lucky
 
Top