Logic Formula: Overall Approval Status
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
-
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
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
-
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
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"
-
@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.
-
@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.
-
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.
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"
-
@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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!