Excel Resources

[AJThomas01]

Please help ...
I get the following error message "Excel ran out of resources while attempting to calculate one or more formulas. As a result these formulas will not be evaluated".
What can I do to correct this and why does it happen, it also seems to be taking longer than usual,i.e. NOT RESPONDING....

 

[GlennUK]

How big are your sheets? Number of formulas? Table sizes? Can you give examples of your most complex formulas, and say how many of that type there are? Or do you have lots of graphic objects embedded? What version of Excel?

 

[AJThomas01]

Hi Glenn,

Doc is about 4MB, I have a number of formulas, the most complex being ...
"=SUM(IF(FREQUENCY(IF('A3 Tactical Report_Final_Eloff.xlsx'!Type="BREA",IF(LEFT('A3 Tactical Report_Final_Eloff.xlsx'!Functional_Location,23)="P-ABER-GBU-020-PRD-010",IF(Equipment<>"",IF(Year=2010,IF(Month=1,MATCH("~"&Equipment&"",Equipment&"",0)))))),ROW(Equipment)+1),1))"

This runs in a number of cells for different months (Jan-Dec) and values get updated on graphs ....

 

[GlennUK]

I don't think that you're going to get a simple answer then. You may want to invest in FastExcel to profile your workbook and analyse your calculations to see where the problem is. Or else destructively remove parts of ( a copy of ) your book until the message disappears, to find out which area is causing it.

 

[AJThomas01]

Thanks Glenn