Progressive Formula
I have a formula that works, but it only works with each independent section, rather than progressively. when one is true by itself, it works on all, but if more than one is true, it does not update to the next "Stage" or section of the formula.
=IF(AND([Task Name]35 = "Foundation Release (Corporate)**", Done35 = 1), "Foundation", IF(AND([Task Name]64 = "Frame Labor", Started64 = 1), "Framing", IF(AND([Task Name]100 = "Roofing Shingles Install", Started100 = 1), "Roofing", IF(AND([Task Name]108 = "HVAC Rough", Started108 = 1), "MEP Roughs", IF(AND([Task Name]149 = "Hang Sheetrock", Started149 = 1), "Sheetrock", IF(AND([Task Name]167 = "Trim 1 (Install Doors and Trim)", Started167 = 1), "Trim 1", IF(AND([Task Name]170 = "Interior Wall-Out", Started170 = 1), "Interior Paint", IF(AND([Task Name]172 = "Cabinet Install", Started172 = 1), "Cabinets", IF(AND([Task Name]180 = "Tile Floors and Bath Walls", Started180 = 1), "Tile", IF(AND([Task Name]193 = "Electric Trim", Started193 = 1), "MEP Trims", IF(AND([Task Name]199 = "Wood Flooring / Stairs Install", Started199 = 1), "Wood Flooring", IF(AND([Task Name]210 = "Interior Final Clean", Started210 = 1), "Final Punch", IF(AND([Task Name]223 = "Quality Walk Sign Off (Manager)**", Done223 = 1), "Complete", "Pre Construction")))))))))))))
Answers
-
Hi Jerry,
This is a testing issue to see where or if there is an error in the statement. And, someone would need to build up a big example sheet to test this. So, you should provide a complete sheet if you are asking the community to test it for you.
When testing a nested function like this, test each IF by itself to make sure they are working as individual statements. Then, add each nested layer one by one back to build up to the final large statement.
Best of Luck
-
I hope you're well and safe!
What if you reverse the formula?
Would that work/help?
I hope that helps!
Have a fantastic weekend & Happy New Year!
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!