Logic Formula: Overall Approval Status

Vivien Chong

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.

Bassam Khalil

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

https://app.smartsheet.com/b/publish?EQBCT=9b55c1522a824756beca2b645cc42313

Bassam Khalil

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

https://app.smartsheet.com/b/publish?EQBCT=9b55c1522a824756beca2b645cc42313

Vivien Chong

@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

@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

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.

Vivien Chong

@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.