Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
expert help needed for better 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.worksheet.functions
|
|||
|
|||
expert help needed for better alternative to slow workbook
.. 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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
expert help needed for better alternative to slow workbook
I set it to manual calculation, but it still takes a full 6 minutes to OPEN
and SAVE. Any other ideas? "Max" wrote: .. 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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
expert help needed for better alternative to slow workbook
"timteebow66" wrote:
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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Expert Needed!!!!!!!!!! Pivot problem | Excel Discussion (Misc queries) | |||
expert with formulas needed again | Excel Discussion (Misc queries) | |||
Complex situation, expert needed... | Links and Linking in Excel | |||
MVP...Formula expert needed!!! | Excel Worksheet Functions | |||
Nested If/Then Math Expert Needed | Excel Worksheet Functions |