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

DCUnited

New Member
Joined
Nov 19, 2005
Messages
2
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.
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

DCUnited

New Member
Joined
Nov 19, 2005
Messages
2
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

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
DCUnited said:
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

Legend
Joined
Sep 3, 2002
Messages
10,473
fairwinds said:
DCUnited said:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,380
Messages
5,571,796
Members
412,419
Latest member
acemali
Top