hi,
I have no idea on expected timeframe but have suggestions for speeding
up the calculation of your formula:
1) Change the vlookups to only lookup the used range, eg instead of:
""=VLOOKUP($A2;'06'!A:H;6;FALSE)"" something like
""=VLOOKUP($A2;'06'!A1:H560;6;FALSE)""
2) Split the repeated vlookups out into separate cells as this will
half the # of times the calculation is done ie, assuming column I &
onwards are empty & the final formula is in column K:
* in column I: ""=VLOOKUP($A2;'06'!A:H;6;FALSE)""
* in column J: ""=VLOOKUP($A2;'tfr 07'!C:H;5;FALSE)""
* in column K:
""=IF(ISERROR(IF(OR($F2<$I2;ISTEXT($J2));$G2/100;0));0;IF(OR($F2<$I2;ISTEXT($J2));$G2/100;0))""
3) Taking the above principle one step further you could enter it as:
* in column I: ""=VLOOKUP($A2;'06'!A:H;6;FALSE)""
* in column J: ""=VLOOKUP($A2;'tfr 07'!C:H;5;FALSE)""
* in column K: ""=IF(OR($F2<$I2;ISTEXT($J2));$G2/100;0)""
* in column L: ""=IF(iserror($k2;0;$k2)""
My internet connection crashed & after a restart Pete had beaten me to
this but sometimes repitition comes in handy, so...
For other suggestions of speeding up your calculation, see:
http://www.decisionmodels.com/optspeedb.htm (& related pages)
Suggestion one & two will have the biggest effect on performance but
three may also make a noticeable difference.
hth,
Rob Brockett
NZ
Always learning & the best way to learn is to experience...
--
broro183
------------------------------------------------------------------------
broro183's Profile:
http://www.excelforum.com/member.php...o&userid=30068
View this thread:
http://www.excelforum.com/showthread...hreadid=574120