Excel Really won't calculate (Yes I know F9)

[DCUnited]

I have a very big file (23MB) that has always worked fine until recently when I added some more columns. The file size increased by about 2MB and now it refuses to calculate.

The file is full of vlookup() and Index() functions but they have always seemed to work in the past with no problem.

I always keep my files on autocalc and even with autocalc I still have the calculate signal in the bottom left. Now it appears everything is calculating correctly with the exception of the charts which won't update...

I contacted Microsoft and even sent them the file but they haven't been able to figure it out (cost me $35).

The guys at Microsoft said it wasn't a problem but I read about a limit on dependent cells, how do you check the number of dependent cells you have in a workbook?

Does anyone know what would cause this to happen? Vlookups? Index? File too large? (although I have a co-worker with a 45mb file that works fine)

Any help would be appreciated.

 

[fairwinds]

Welcome to the board!


I believe it calculates but still shows "calculate" because of too many dependants.

See:

http://www.decisionmodels.com/calcsecretsf.htm

 

[just_jon]

I think it sounds like you've overflowed XL's ability to maintain the dependency tree.

See the link below about full calculation with tree rebuild --

http://www.decisionmodels.com/calcsecretsg.htm

 

[DCUnited]

Thank you both...

In this case where I have hit the limit what makes more sense to use Index or Vlookup?

If I have a vlookup range of (A2:B4) is that 4 dependencies?

If I have an Index range of (B2:B4) is that 2 dependencies.

If the cell just says =B2 is that 1 dependent?

I guess I still don't fully understand what a dependent cell is...

Has anyone else run into this problem and if so what did you do to fix it?

 

[just_jon]

Dependencies have to do with what cells need to be calculated before other cells can be computed.

If cell A1 references cell B1, then B1 must be computed 1st.

If cell B1 itself references cell C1, then C1 must have its value before either of the others.

Give us an idea of how many cells - about - have formulas, how many sheets they are on, and what type formulas - give examples - they are.

Also, go back to the Decision models web site and read up on fast computing tips.

 

[fairwinds]

Has anyone else run into this problem and if so what did you do to fix it?

Sure, pretty often I see monstrous files that are unstable and take long time to calculate. You have too start from the beginning. Can it be done smarter? More efficient? Most certain. Maybe a VBA approach is required, a macro that you can run when needed instead of formulas that constantly recalculates.

 

[just_jon]

Has anyone else run into this problem and if so what did you do to fix it?

Sure, pretty often I see monstrous files that are unstable and take long time to calculate. You have too start from the beginning. Can it be done smarter? More efficient? Most certain. Maybe a VBA approach is required, a macro that you can run when needed instead of formulas that constantly recalculates.

fairwinds is right ( as usual ) - in addition to more efficient formula sets there's also the possibility of using code to all or part of your current formula calculation.