Nested IF/AND/OR
I'm modifying a formula used for status tracking and I'm running into an unparseable error that I'm having problems resolving. I want to make the status red (stop sign) if the status is "in progress" or "not started" when the "at risk" column is checked. My understanding is that the formula will look for the first true statement and then stop looking.
Original formula:
=IF(Summary@row = 1, "", IF(Status@row = "Complete", "☑️", IF(Status@row = "Cancelled", "✖️", IF(AND(OR(Start@row = "", [Planned Finish]@row = ""), Status@row = "In Progress"), "🟢", IF(OR(Start@row = "", [Planned Finish]@row = ""), "🔘", IF(AND(OR(Status@row <> "Completed", Status@row <> "Completed", Status@row <> "Completed"), [Planned Finish]@row < Today@row), "🛑", IF(AND(Status@row = "Not Started", Start@row < Today@row), "⚠️", IF(Status@row = "In Progress", "🟢", "🔘"))))))))
Revised formula (addition after cancelled icon)
=IF(Summary@row = 1, "", IF(Status@row = "Complete", "☑️", IF(Status@row = "Cancelled", "✖️", IF(AND(OR(Status@row = "Not Started", Status@row = "In Progress"), [At Risk]@row = 1), "🛑"), IF(AND(OR(Start@row = "", [Planned Finish]@row = ""), Status@row = "In Progress"), "🟢", IF(OR(Start@row = "", [Planned Finish]@row = ""), "🔘", IF(AND(OR(Status@row <> "Completed", Status@row <> "Completed", Status@row <> "Completed"), [Planned Finish]@row < Today@row), "🛑", IF(AND(Status@row = "Not Started", Start@row < Today@row), "⚠️", IF(Status@row = "In Progress", "🟢", "🔘")))))))))
Any ideas?
Answers
-
.... and I found a solution: There was an extra closing parenthesis after the first stop sign icon
=IF(Summary@row = 1, "", IF(Status@row = "Complete", "☑️", IF(Status@row = "Cancelled", "✖️", IF(AND(OR(Status@row = "Not Started", Status@row = "In Progress"), [At Risk]@row = 1), "🛑", IF(AND(OR(Start@row = "", [Planned Finish]@row = ""), Status@row = "In Progress"), "🟢", IF(OR(Start@row = "", [Planned Finish]@row = ""), "🔘", IF(AND(OR(Status@row <> "Completed", Status@row <> "Completed", Status@row <> "Completed"), [Planned Finish]@row < Today@row), "🛑", IF(AND(Status@row = "Not Started", Start@row < Today@row), "⚠️", IF(Status@row = "In Progress", "🟢", "🔘")))))))))
-
Hi @jking108
I hope you're well and safe!
Excellent! Glad you got it working!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Please support the Community by marking your post with the accepted answer/helpful. 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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!