View Single Post
  #14   Report Post  
wickedchew wickedchew is offline
Senior Member
 
Location: Philippines
Posts: 161
Default

Quote:
Originally Posted by scubadivingfool View Post
98% perfect. Just one more thing. The last three duties need to be done by the people on the CLOSE and not the MID.

Thanks
Do this for each worksheet for cells G36, G37 and G38

Replace the word MID with CLOSE

BEFORE

=IF(B37="","",IF(IF(VLOOKUP(OFFSET($AB$11,MATCH(IN DEX($G$30:$G$38,MATCH(MAX($AE$30:AE36),$AE$30:AE36 ,0)),$AB$11:$AB$28,0),0,1,1),$AB$11:$AC$28,2,0)="O pen",OFFSET($AB$11,MATCH("Mid",$AC$11:$AC$28,0),0, 1,1),OFFSET($AB$11,MATCH(INDEX($G$30:$G$38,MATCH(M AX($AE$30:AE36),$AE$30:AE36,0)),$AB$11:$AB$28,0),0 ,1,1))="",OFFSET($AB$11,MATCH("Mid",$AC$11:$AC$28, 0)-1,0,1,1),IF(VLOOKUP(OFFSET($AB$11,MATCH(INDEX($G$3 0:$G$38,MATCH(MAX($AE$30:AE36),$AE$30:AE36,0)),$AB $11:$AB$28,0),0,1,1),$AB$11:$AC$28,2,0)="Open",OFF SET($AB$11,MATCH("Mid",$AC$11:$AC$28,0),0,1,1),OFF SET($AB$11,MATCH(INDEX($G$30:$G$38,MATCH(MAX($AE$3 0:AE36),$AE$30:AE36,0)),$AB$11:$AB$28,0),0,1,1))))
AFTER

=IF(B37="","",IF(IF(VLOOKUP(OFFSET($AB$11,MATCH(IN DEX($G$30:$G$38,MATCH(MAX($AE$30:AE36),$AE$30:AE36 ,0)),$AB$11:$AB$28,0),0,1,1),$AB$11:$AC$28,2,0)="O pen",OFFSET($AB$11,MATCH("Close",$AC$11:$AC$28,0), 0,1,1),OFFSET($AB$11,MATCH(INDEX($G$30:$G$38,MATCH (MAX($AE$30:AE36),$AE$30:AE36,0)),$AB$11:$AB$28,0) ,0,1,1))="",OFFSET($AB$11,MATCH("Close",$AC$11:$AC $28,0)-1,0,1,1),IF(VLOOKUP(OFFSET($AB$11,MATCH(INDEX($G$3 0:$G$38,MATCH(MAX($AE$30:AE36),$AE$30:AE36,0)),$AB $11:$AB$28,0),0,1,1),$AB$11:$AC$28,2,0)="Open",OFF SET($AB$11,MATCH("Close",$AC$11:$AC$28,0),0,1,1),O FFSET($AB$11,MATCH(INDEX($G$30:$G$38,MATCH(MAX($AE $30:AE36),$AE$30:AE36,0)),$AB$11:$AB$28,0),0,1,1)) ))

Last edited by wickedchew : August 4th 10 at 06:03 PM Reason: Ugly formula, donchatink??