Multiple If statements
Hi, hoping someone will be able to help with an issue I am having with an IF formula.
I am attempting to build an IF formula from check boxes, however it isn't as simple as multiple IF statements returning their own responses.
What I am trying to achieve is a status update column, based off of the checkboxes. And therefore as the process moves onto the next step, the previous step (tick in a checkbox column) will need to have been completed to return the correct status update.
For example:
Site Visit Complete is checked - return "Visit Complete" in the Status column
Site Visit Complete AND Write up Complete are checked - return "Write Up Complete"
Site Visit Complete AND Write up Complete AND Admin QC Complete are checked - return "Admin QC Complete"
Site Visit Complete AND Write up Complete AND Admin QC Complete AND Tech Review Complete are checked - return "Tech QC Complete"
Site Visit Complete AND Write up Complete AND Admin QC Complete AND Tech Review Complete AND Issued to the Client are checked - return "Issued to Client"
I also need to add in an On Hold and Removed From Scope check box to the statement - I assume these would go at the start and they wouldn't need to be connected to any of the other IF statements.
What I have so far:
=IF([On Hold]@row = 1, "On Hold", IF([Removed from Scope]@row = 1, "Removed from Scope")
If anyone can offer some guidance that would be great.
Thank you, Alicia.
Best Answer
-
=IF([On Hold]@row = 1, "On Hold", IF([Removed from Scope]@row = 1, "Removed from Scope", IF(AND([Site Visit Complete]@row = 1, [Write Up Complete]@row = 1, [Admin QC Complete]@row = 1, [Tech Review Complete]@row = 1, [Issued to the Client]@row = 1), "Issued to Client", IF(AND([Site Visit Complete]@row = 1, [Write Up Complete]@row = 1, [Admin QC Complete]@row = 1, [Tech Review Complete]@row = 1), "Tech Review Complete", IF(AND([Site Visit Complete]@row = 1, [Write Up Complete]@row = 1, [Admin QC Complete]@row = 1), "Admin QC Complete", IF(AND([Site Visit Complete]@row = 1, [Write Up Complete]@row = 1), "Write Up Complete", IF([Site Visit Complete]@row = 1, "Site Visit Complete", "")))))))
Answers
-
=IF([On Hold]@row = 1, "On Hold", IF([Removed from Scope]@row = 1, "Removed from Scope", IF(AND([Site Visit Complete]@row = 1, [Write Up Complete]@row = 1, [Admin QC Complete]@row = 1, [Tech Review Complete]@row = 1, [Issued to the Client]@row = 1), "Issued to Client", IF(AND([Site Visit Complete]@row = 1, [Write Up Complete]@row = 1, [Admin QC Complete]@row = 1, [Tech Review Complete]@row = 1), "Tech Review Complete", IF(AND([Site Visit Complete]@row = 1, [Write Up Complete]@row = 1, [Admin QC Complete]@row = 1), "Admin QC Complete", IF(AND([Site Visit Complete]@row = 1, [Write Up Complete]@row = 1), "Write Up Complete", IF([Site Visit Complete]@row = 1, "Site Visit Complete", "")))))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!