Originally Posted by
Yongle
To also check for blanks try
=IF(OR(Agency_Contract!X9=0,Agency_Contract!X9="")*1=1,"XXX",IF(J9>Agency_C ontract!X9,J9-Agency_Contract!X9,VLOOKUP(D9,Agency_Contract,22,1)))
Explanation
The bit inside the OR statement returns a TRUE value if both conditions are met, and FALSE otherwise
To turn that into something we can use we multiply by 1 and TRUE becomes the number 1 and FALSE becomes the number 0
=VLOOKUP(Agency_Daily_Input!D9,Agency_Contract!$C$9:$T$10000,IF(AND(WEEKDAY(Agency_Daily_Input!B9)>1,WEEKDAY(Agency_Daily_Input!B9)<7),13,IF(WEEKDAY(Agency_Daily_Input!B9)=7,15,IF(WEEKDAY(Agency_Daily_Input!B9)=1,17)+IF(Agency_Daily_Input!G9>0.708333333333333,1))),FALSE)
i need the below code to do what the above code does with regards to looking up who the contract is
=IF(Agency_Contract!X9=0,"",IF(J9>Agency_Contract!X9,J9-Agency_Contract!X9,VLOOKUP(D9,Agency_Contract!C9:Y10,22,1)))
here it he link to my file
ive highlight the cell im talking about in yellow
https://www.dropbox.com/s/hni5spbtti...orum.xlsm?dl=0
Reply With Quote