Logic Formula: Overall Approval Status

Options

I need help to get this formula right. I have the following scenario but I have no idea how to put the logic into a formula statement.


With reference to the following columns, here are the scenario:

1) There are a series of approvals needed from 5 different person

2) If any Approver is "Approval Request Submitted", then the Status should be "Waiting for Approval"

3) If any Approver is "Denied", then the Status should be "Denied"

4) If all Approver is "Approved", then the Status should be "Approved"

5) If any Approver is "Skipped", while all others are "Approved", the Status should be "Approved"


Appreciate assistance. Thank you.

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 01/27/21 Answer ✓
    Options

    Hi @Vivien Chong

    Hope you are fine, i hope the following formula will solve your question.

    =IFERROR(IF(OR([Approver 1 Status]@row = "Denied", [Approver 2 Status]@row = "Denied", [Approver 3 Status]@row = "Denied", [Approver 4 Status]@row = "Denied", [Approver 5 Status]@row = "Denied"), "Denied", (IF(OR([Approver 1 Status]@row = "Submitted", [Approver 2 Status]@row = "Submitted", [Approver 3 Status]@row = "Submitted", [Approver 4 Status]@row = "Submitted", [Approver 5 Status]@row = "Submitted"), "Waiting For Approval", (IF(OR([Approver 1 Status]@row = "Approved", [Approver 2 Status]@row = "Approved", [Approver 3 Status]@row = "Approved", [Approver 4 Status]@row = "Approved", [Approver 5 Status]@row = "Approved"), "Approved"))))), "")

    you can check the following sample sheet



    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 01/27/21 Answer ✓
    Options

    Hi @Vivien Chong

    Hope you are fine, i hope the following formula will solve your question.

    =IFERROR(IF(OR([Approver 1 Status]@row = "Denied", [Approver 2 Status]@row = "Denied", [Approver 3 Status]@row = "Denied", [Approver 4 Status]@row = "Denied", [Approver 5 Status]@row = "Denied"), "Denied", (IF(OR([Approver 1 Status]@row = "Submitted", [Approver 2 Status]@row = "Submitted", [Approver 3 Status]@row = "Submitted", [Approver 4 Status]@row = "Submitted", [Approver 5 Status]@row = "Submitted"), "Waiting For Approval", (IF(OR([Approver 1 Status]@row = "Approved", [Approver 2 Status]@row = "Approved", [Approver 3 Status]@row = "Approved", [Approver 4 Status]@row = "Approved", [Approver 5 Status]@row = "Approved"), "Approved"))))), "")

    you can check the following sample sheet



    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Vivien Chong
    Vivien Chong ✭✭✭✭✭✭
    Options

    @Bassam.M Khalil thanks for your prompt reply, let me try the formula and revert on this.

    On the last part of the formula, it should be "when all approved, then approved", I think I should change the "OR" to "AND"?

    Let me work it out see if it works. Thanks once again.

  • Vivien Chong
    Vivien Chong ✭✭✭✭✭✭
    Options

    @Bassam.M Khalil I have tested the formula, and now I understand why you put "OR" for the last part of the formula.

    As I tested it for my use case, I now wondered if we can put some form of precedence.

    The approval process goes from 1 to 2 to 3 to 4 to 5.

    If it is denied along the way, amendment will be made and the approval process starts all over again.

    For example: If Approver 4 DENY, the Status will show DENIED.

    Changes will be made and the approval process starts again at Approver 1.

    However, the Status is still showing DENIED when the entire process restarted.

    I know this may be difficult and we may have to make do with it, but just throwing out the question if there are ways that I do not know.

    Thanks.

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    Hi @Vivien Chong

    Hope you are fine, you can do the approval sequence by using an automated approval workflow also you can restart the process if anyone denied by an automated update request workflow. i can design the whole process for you with all possibilities but it will take time, if you want me to do it please call me on my WhatsApp ( +966530973300 ) to arrange for a meeting to discuss all that you need.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Vivien Chong
    Vivien Chong ✭✭✭✭✭✭
    Options

    @Bassam.M Khalil thanks for your suggestion. I am doing that for the workflow automation.

    Probably I didn't make myself clear. When the process is restarted do to a "Denied" for amendment to be made, the overall status (the one we are using the formula) is still showing DENIED. I am just thinking, how can we at least have this as "Waiting for Approval". Trying to figure out if there's a way to get this.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!