Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Expert help for alternative to slow workbook
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$5 000,"<"& 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),C OUNTIF(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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Expert help for alternative to slow workbook
have you tried:
Tools-Options-Calculations-Manual (before Excel 2007) or Options-Formulas-Worksheet Calculation-Manual (i don't have an English version of Excel 2007, hope it the right name) ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
expert help needed for better alternative to slow workbook | Excel Worksheet Functions | |||
Copying data in a form into an alternative Workbook | Excel Discussion (Misc queries) | |||
UDF alternative to PULL that opens external workbook and grabs val | Excel Worksheet Functions | |||
Slow workbook | Excel Discussion (Misc queries) | |||
Slow Array formula or is there any alternative | Excel Programming |