T
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.
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.