Solved

# Updating status based on set criteria

Posted on 2014-11-20

I am trying to compare "ES NEG POS Date" with "PV DETAILS THIS WK-S Date" and "ES NEG POS Amount" with "PV DETAILS THIS WK-S Amount" and update the status("Fixed" or "Unfixed") based on the below mentioned criteria.

Criteria based on Date

ES NEG POS Date PV DETAILS THIS WK-S Date Status

20-Nov-14 20-Nov-14 Fixed

20-Nov-14 5-Nov-14 Fixed

20-Nov-14 25-Nov-14 Unfixed

Criteria based on Amount

ES NEG POS Amount PV DETAILS THIS WK-S Amount Status

50.00 50.00 Fixed

50.00 40.00 Fixed

-50.00 -40.00 Fixed

0.00 50.00 Unfixed

0.00 -50.00 Unfixed

50.00 90.00 Unfixed

-50.00 -90.00 Unfixed

-50.00 30.00 Unfixed

30.00 -50.00 Unfixed

Status after considering date and amount together

Status based on Date Status based on Amount Final status

Fixed Fixed Fixed

Fixed Unfixed Unfixed

Unfixed Fixed Unfixed

Fixed Unfixed Unfixed

One of my team members is doing this manually for at least 10 sheets. I tried to write “If/And/OR” logic as mentioned below, but was not successful completely. I am finding it difficult because of negative (-) number. Please help

For Date: IF(OR(E2=S2,E2=R2,E2<S2,E2<R2),"Fixed","Unfixed")

For Amount: =IF(T2=0,"Unfixed",IF(M2=T2,"Fixed",IF(AND(T2<0,M2>0),"UnFixed",IF(AND(M2<0,T2>0),"UnFixed",IF(AND(T2<0,M2<0,T2<M2),"Fixed",IF(AND(T2<0,M2<0,M2<T2),"UnFixed",IF(T2>M2,"Fixed",IF(M2>T2,"UnFixed",""))))))))

Hence curious to know if we can write a VB script for accurate results or any other better method I can use to get the right status.

Any suggestions would be greatly appreciated!

I am looking forward for the immediate and positive response!!!!!!

Thanks,

Ganesh