expert help needed for better alternative to slow workbook

[timteebow66]

I inherited a workbook that has 2 sheets. It takes TOO LONG to open, save
and even locks up the computer sometimes. There are 2 sheets involved. The
first sheet is named BudgetCost, and the second is named CostAnalysis. The
purpose of the work book is to have CostAnalysis sort through BudgetCost and
list all the material and costs that are associated to any one particular
ClientID. Here is how each sheet looks:

BudgetCost is basically the data entry sheet. The first 2 rows are blank
for some reason. The columns have the following named titles:
A3=ClientID
B3=Material
C3=Budget Cost
D3=Analysis Cost

CostAnalysis is the complex sheet with complex formulas. Columns A,B,C,D
are the only seen colums. The rest are hidden, but contain formulas. When a
ClientID is entered into A1 of this sheet, the sheet automatically fills in
data in columns A,B,C. Here is how it looks:

Row1 only has A1 to enter data into. A1 is validated with a
=OFFSET($E$4,0,0,COUNT(F:F),1)

Row 2 is blank

A3=IF(ISERR(OFFSET(BudgetCost!$A$1,I4-1,0)),"",OFFSET(BudgetCost!$A$1,I4-1,0))

B3=IF(ISERR(OFFSET(BudgetCost!$B$1,I4-1,0)),"",OFFSET(BudgetCost!$B$1,I4-1,0))

C3=IF(ISERR(OFFSET(BudgetCost!$C$1,I4-1,0)),"",OFFSET(BudgetCost!$C$1,I4-1,0))

D3=IF(ISERR(OFFSET(BudgetCost!$D$1,I4-1,0)),"",OFFSET(BudgetCost!$D$1,I4-1,0))

E3=IF(F4="","",OFFSET(BudgetCost!$A$1,F4-1,0))

F3=IF(OR(ISERR(SMALL(G:G,ROW(1:1))),BudgetCost!A4=""),"",MID(SMALL(G:G,ROW(1:1)),FIND(".",SMALL(G:G,ROW(1:1))),6)*100000)

G3=IF(OR(BudgetCost!A4="",COUNTIF(BudgetCost!$A$4:$A4,
BudgetCost!$A4)>1),"",COUNTIF(BudgetCost!$A$4:$A$5000,"<"&
BudgetCost!$A4)+1+ROW()/100000)

H3 is blank

I3=IF(BudgetCost!C4="","",MID(SMALL(K:K,ROW(1:1)),FIND(".",SMALL(K:K,ROW(1:1))),6)*100000)

J3=IF(OR(BudgetCost!A4="",
BudgetCost!A4<>$A$1),"",IF(ISTEXT(BudgetCost!C4),COUNTIF(BudgetCost!$C$4:$C$50,"<"& BudgetCost!$C4)+1+ROW()/100000,""))

K3=IF(OR(BudgetCost!A4="",
BudgetCost!A4<>$A$1),"",IF(ISNUMBER(BudgetCost!C4),RANK(BudgetCost!C4,
BudgetCost!$C$4:$C$5000,1)+COUNTIF(BudgetCost!$C$4:$C$5000,"*")+ROW()/100000,J4))

This is beyond me, and I have been given a very short deadline at work to
find a better way to get the same results without the slow and lockup
problems. It doesn't help that I have to do this on excel 2007 b/c it is new
to me. Can any expert help me? It would be great if someone could even
attach the solution in excel format to their response. Thanks.

 

[Max]

.. It takes TOO LONG to open, save

and even locks up the computer sometimes...

Without touching anything (imo, it's best to leave all those complex
**working** formulas alone), think you could dramatically improve the
situation faced via simply setting calculation to Manual mode, with "Recalc
before save" option left *unchecked*.

In xl2003 (I don't have xl2007), the options to set the mode are available
via clicking Tools > Options > Calculation tab. With the manual mode setting,
you just need to press F9 only whenever recalcs are required, eg upon
completing a batch of input work. You can & should save the file frequently
as inputs proceed, w/o recalculating.

---

 

[timteebow66]

I set it to manual calculation, but it still takes a full 6 minutes to OPEN
and SAVE. Any other ideas?

 

[Max]

I set it to manual calculation, but it still takes a full 6 minutes to OPEN
and SAVE. Any other ideas?

a. As Excel sets the calc mode for each session following that of the first
book that's opened (the first book opened could well be set to Automatic),
just be aware of this event. That means even if your particular file is saved
at manual, it will open in Automatic mode (hence the delay?). Maybe try
opening just this particular file directly whenever you open it. And remember
to *uncheck* "Recalc before save"

b. Try the calculation secrets, optimising speed pages, etc on Charles'
website:
http://www.decisionmodels.com/
(Maybe Charles himself might drop by here, too?)

c. Test these changes carefully in a spare copy
1. Try replacing the DV? dynamic range below with a fixed range
=OFFSET($E$4,0,0,COUNT(F:F),1)

2. In A3 to D3, see whether you can live w/o the error trap, eg in A3:
=IF(ISERR(OFFSET(BudgetCost!$A$1,I4-1,0)),"",OFFSET(BudgetCost!$A$1,I4-1,0))
Replace A3 with just: =OFFSET(BudgetCost!$A$1,I4-1,0)
Similarly in B3 to D3. It might look ugly on the sheet, but if the error
returns in these cells do not have dependents elsewhere, it's tolerable.

3. Try replacing the front IF part in F3

=IF(OR(ISERR(SMALL(G:G,ROW(1:1))),BudgetCost!A4=""),"",...

with this:
=IF(BudgetCost!A4="","",IF(ROWS($1:1)>COUNT(G:G),"",...
(Think it should be faster to check the single cell A4 first, then to use
simplier constructs in the next IF check)

Of course, after replacing the top cells above, you should fill down as
required.

---