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.

Improve this question

1 Answer 1

Reset to default
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
6
  • 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. Commented May 3, 2020 at 14:26
  • You need to add an IF block for the TRUE condition section of 'Treasury Status]="APPROVED"' section. Check the edited formula. Commented May 3, 2020 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. Commented May 3, 2020 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. Commented May 3, 2020 at 15:57
  • Yeah that's right UBK, missed the false condition. It worked perfectly. Thank you very much:) Commented May 3, 2020 at 16:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.