Update a status column based on multiple conditions

I have a sheet tracking a project wherein the status will be changed multiple times depending on the phase of the project. Right now I'm trying to set up an autofill of the status to say "Director Approval" when the approval workflow is received by the director, then I want the status to update when the next approval is received by the Executive Director. The formula i have works, except that it will only recognize the first condition. It will not change the status when a new condition is added.
This is what I have:
=IF([CCA FORM RECEIVED]@row = 1, "CCA FORM RECEIVED", IF([Director Approval]@row = "Approved", "DIRECTOR APPROVED", IF([ED Approved]@row = "Approved", "EXECUTIVE DIRECTOR APPROVAL")))
I played around adding an OR at the front of each, but that didn't work.
Thank you!
Best Answers
-
Hi Christina,
Try reversing the order of the tests from last to first, ie check for the last status first. The problem is that once the CCA FORM RECEIVED is set to 1, your IF statement returns true so it never goes on to the next test.
Regards,
Andrew
-
Hi Christina,
I agree with Andrew.
Try this.
=IF([ED Approved]@row = "Approved", "EXECUTIVE DIRECTOR APPROVAL",IF([Director Approval]@row = "Approved", "DIRECTOR APPROVED",IF([CCA FORM RECEIVED]@row = 1, "CCA FORM RECEIVED")))
Did it work?
I hope that helps!
Have a fantastic day!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Please help the Community by marking the post that helped answer your question or solve your problem with 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.
-
Thank you both! This seems to work so far.
I really appreciate it!
Answers
-
Hi Christina,
Try reversing the order of the tests from last to first, ie check for the last status first. The problem is that once the CCA FORM RECEIVED is set to 1, your IF statement returns true so it never goes on to the next test.
Regards,
Andrew
-
Hi Christina,
I agree with Andrew.
Try this.
=IF([ED Approved]@row = "Approved", "EXECUTIVE DIRECTOR APPROVAL",IF([Director Approval]@row = "Approved", "DIRECTOR APPROVED",IF([CCA FORM RECEIVED]@row = 1, "CCA FORM RECEIVED")))
Did it work?
I hope that helps!
Have a fantastic day!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
✅Please help the Community by marking the post that helped answer your question or solve your problem with 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.
-
Thank you both! This seems to work so far.
I really appreciate it!
-
Excellent!
Happy to help!
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!