Quote:
Originally Posted by scubadivingfool
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)) ))