0

can anyone please help me in cracking the logic in below if statement, that i am trying to solve.

Below is the logic i am trying

  • three columns Item status, Treasury status and MD status
  • if Treasury status and MD status are blank, item status should say Pending
  • if Treasury status say approved, then Item status should change to Approved by treasury
  • if treasury status say rejected, then item status should change to requester need resubmission
  • If MD status say Approved, then item status should change to completed.
  • If MD status say rejected, then item status should change to requester need resubmission

Below is the formulae i got

=IF(NOT([MD Status]=""),"COMPLETED",IF([Treasury Status]="APPROVED","APPROVED BY TREASURY",IF([Treasury Status]="REJECTED","REQUESTER NEED RESUBMISSION",IF([MD Status]="REJECTED","REQUESTER NEED RESUBMISSION","PENDING"))))

All logics are working, but when MD status id changed to REJECTED, Item status not changing to Request need resubmission. It is changing to complted.

Please help. Raj.

0

Something like this?

=IF(
    AND(ISBLANK([MD Status]),ISBLANK([Treasury Status])),
    "PENDING",
    IF([Treasury Status]="APPROVED",
        IF([MD Status]="REJECTED",
            "REQUESTER NEED RESUBMISSION",
            IF([MD Status]="APPROVED",
                "COMPLETED",
                "APPROVED BY TREASURY"
            )
        ),
        IF([Treasury Status]="REJECTED",
            "REQUESTER NEED RESUBMISSION",
            IF([MD Status]="APPROVED",
                "COMPLETED",
                IF([MD Status]="REJECTED",
                    "REQUESTER NEED RESUBMISSION",
                    "PENDING"
                )
            )   
        )
    )   
)
| improve this answer | |
  • Hi UBK, thanks for reply. Actually i forgot to mention on more condition. Even if treasury status say approved and md status rejected the item status should say requetser need resubmission. But with your formulae the item status showing as approved by treasury. – Raj May 3 at 14:26
  • You need to add an IF block for the TRUE condition section of 'Treasury Status]="APPROVED"' section. Check the edited formula. – UBK May 3 at 14:52
  • Hi UBK, thanks. For condition i asked yes it worked. But got one more issue. If treasury status and MD status approved, item status should show completed, but it is showing approved by treasury. – Raj May 3 at 15:43
  • You could have added it for the [MD Status]="REJECTED" FALSE condition. I have updated the post, mark it as answer if it helped you to understand the idea behind nested IF conditions. – UBK May 3 at 15:57
  • Yeah that's right UBK, missed the false condition. It worked perfectly. Thank you very much:) – Raj May 3 at 16:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.