Changing a cell value based on multiple criteria
Hi everyone. I have been attempting this formula all day with no luck. Hopefully someone here can help out.
I have a sheet with the following columns: Approved, Approval Need, Sr. Dir Comm Sol Sales Ops Approval, MA Dir. Comm Sol Sales Ops Approval, Reg Sr. Dir Comm Sol Sales Ops Approval, VP Approval
The Approved Column (symbol) has the following options: Yes, Hold, No
The Approval Needed column has the following options: No, Send for Concurrent Approvals, VP Approval Needed
The other four columns have the same options which are: Submitted, Approved, Declined.
When a row is created via a form it will automatically assign an option based on who submits the form, it will either be No or Send for Concurrent Approvals. Three separate approval workflows will go out (these are the concurrent approvals) to the Sr. Dir Comm Sol Sales Ops Approval, MA Dir. Comm Sol Sales Ops Approval, Reg Sr. Dir Comm Sol Sales Ops Approval. There is no hierarchy here which is why it isn't one approves then it moves to the next.
I need a formula to change the cell value in the Approved column based on the following criteria:
If the value in Approval Needed is No then the value in Approved should be Yes
If the Value in Approval needed is Send for Concurrent Approvals AND the values in Sr. Dir Comm Sol Sales Ops Approval, MA Dir. Comm Sol Sales Ops Approval, and Reg Sr. Dir Comm Sol Sales Ops Approval are ALL Approved then the value in Approved should be Yes.
If the Value in Approval Needed is Send for Concurrent Approvals AND the values in Sr. Dir Comm Sol Sales Ops Approval, MA Dir. Comm Sol Sales Ops Approval, and Reg Sr. Dir Comm Sol Sales Ops Approval are ALL Submitted then the value in Approved should be Hold.
If the Value in Approval Needed is Send for Concurrent Approvals AND the values in any one of Sr. Dir Comm Sol Sales Ops Approval, MA Dir. Comm Sol Sales Ops Approval, or Reg Sr. Dir Comm Sol Sales Ops Approval are Approved but one or two are still Submitted then the Value in Approved should remain Hold until ALL are Approved. For instance Sr. Dir Comm Sol Sales Ops Approval is Approved but MA Dir. Comm Sol Sales Ops Approval and Reg Sr. Dir Comm Sol Sales Ops Approval are both Submitted then Approval would still show Hold.
If the Value in Approval Needed is Send for Concurrent Approvals AND the value in any one of Sr. Dir Comm Sol Sales Ops Approval, MA Dir. Comm Sol Sales Ops Approval, or Reg Sr. Dir Comm Sol Sales Ops Approval is Declined then the value in Approval should be No.
If after reviewing one of the approvers decides that it requires VP approval they can change the Approval Needed to VP Approval Needed which will trigger another approval workflow to go to the VP. The approved column should then follow the same basic logic
If the Value in Approval Needed is VP Approval Needed and the Value in VP Approval is Submitted the Value in Approved should be Hold
If the Value in Approval Needed is VP Approval Needed and the Value in VP Approval is Approved the Value in Approved should be Yes
If the Value in Approval Needed is VP Approval Needed and the Value in VP Approval is Declined the Value in Approved should be No.
Any help or ideas on how to make this easier would be appreciated!
Best Answer
-
Hey Christopher, Does this work for you?
I interpreted "The Approved Column (symbol) has the following options: Yes, Hold, No" as:
Red=No,
Yellow=On Hold
Green=Yes
Proposed formula:
=IF(OR([Sr. Dir Comm Sol Sales Ops Approval]@row = "Declined", [MA Dir. Comm Sol Sales Ops Approval]@row = "Declined", [Reg Sr. Dir Comm Sol Sales Ops Approval]@row = "Declined", [VP Approval]@row = "Declined"), "Red", IF(OR([Approval Needed]@row = "No", AND([Approval Needed]@row = "Send for Concurrent Approvals", [Sr. Dir Comm Sol Sales Ops Approval]@row = "Approved", [MA Dir. Comm Sol Sales Ops Approval]@row = "Approved", [Reg Sr. Dir Comm Sol Sales Ops Approval]@row = "Approved"), AND([Approval Needed]@row = "VP Approval Needed", [VP Approval]@row = "Approved")), "Green", "Yellow"))
This reads as:
The symbol will be red if any of the four columns equal "declined"
ELSE
The symbol will be green if any of the following conditions are met:
No approval needed OR
Only concurrent approvals are needed and all three directors approved OR
VP approval is needed and the VP approved
ELSE
The symbol will be yellow
Answers
-
Hey Christopher, Does this work for you?
I interpreted "The Approved Column (symbol) has the following options: Yes, Hold, No" as:
Red=No,
Yellow=On Hold
Green=Yes
Proposed formula:
=IF(OR([Sr. Dir Comm Sol Sales Ops Approval]@row = "Declined", [MA Dir. Comm Sol Sales Ops Approval]@row = "Declined", [Reg Sr. Dir Comm Sol Sales Ops Approval]@row = "Declined", [VP Approval]@row = "Declined"), "Red", IF(OR([Approval Needed]@row = "No", AND([Approval Needed]@row = "Send for Concurrent Approvals", [Sr. Dir Comm Sol Sales Ops Approval]@row = "Approved", [MA Dir. Comm Sol Sales Ops Approval]@row = "Approved", [Reg Sr. Dir Comm Sol Sales Ops Approval]@row = "Approved"), AND([Approval Needed]@row = "VP Approval Needed", [VP Approval]@row = "Approved")), "Green", "Yellow"))
This reads as:
The symbol will be red if any of the four columns equal "declined"
ELSE
The symbol will be green if any of the following conditions are met:
No approval needed OR
Only concurrent approvals are needed and all three directors approved OR
VP approval is needed and the VP approved
ELSE
The symbol will be yellow
-
The below should work it will also say hold as well if any of the approval columns are blank and the Approval Needed is VP Approval Needed or any of the Approval columns are blank except the VP Approval column and the approval needed is not NO.
=IF([Approval Needed]@row = "No", "Yes", IF(OR([SR Dir Comm Sol Sales Ops Approval]@row = "Declined", [MA Dir. Comm Sol Sales Ops Approval]@row = "Declined", [Reg Sr. Dir Comm Sales Ops Approval]@row = "Declined", [VP Approval]@row = "Declined"), "No", IF(OR([SR Dir Comm Sol Sales Ops Approval]@row = "Submitted", [MA Dir. Comm Sol Sales Ops Approval]@row = "Submitted", [Reg Sr. Dir Comm Sales Ops Approval]@row = "Submitted", [VP Approval]@row = "Submitted"), "Hold", IF(AND([SR Dir Comm Sol Sales Ops Approval]@row = "Approved", [MA Dir. Comm Sol Sales Ops Approval]@row = "Approved", [Reg Sr. Dir Comm Sales Ops Approval]@row = "Approved", [VP Approval]@row = "Approved"), "Yes", IF(AND([Approval Needed]@row = "Send for Concurrent Approvals", [SR Dir Comm Sol Sales Ops Approval]@row = "Approved", [MA Dir. Comm Sol Sales Ops Approval]@row = "Approved", [Reg Sr. Dir Comm Sales Ops Approval]@row = "Approved"), "Yes", IF(AND([Approval Needed]@row <> "No", OR([SR Dir Comm Sol Sales Ops Approval]@row = "", [MA Dir. Comm Sol Sales Ops Approval]@row = "", [Reg Sr. Dir Comm Sales Ops Approval]@row = "")), "Hold", IF(AND([Approval Needed]@row = "VP Approval Needed", OR([SR Dir Comm Sol Sales Ops Approval]@row = "", [MA Dir. Comm Sol Sales Ops Approval]@row = "", [Reg Sr. Dir Comm Sales Ops Approval]@row = "", [VP Approval]@row = "")), "Hold")))))))
-
I hope you're well and safe!
Have you explored using Workflows instead?
Would that work/help?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
This worked (I think I was just overthinking it) except for the symbols. I too thought of Red, Green, and Yellow at first except it actually was Yes, No, and Hold. Once I changed to those values it worked perfectly. Thank you.
-
Unfortunately it doesn't look like it is an option to change a cell value in a symbol column using a workflow.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 494 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
