Progressive Formula

Options

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

  • Mary_A
    Mary_A ✭✭✭✭✭✭
    Options

    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

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @Jerry Alexander

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!