Identify current phase based on Status of summary task (rather than % completed)

I am looking to identify the current phase of a project and pull this into the Sheet Summary for reporting. In this image, I would look to identify the current as "Deployment - Prep" as previous phases are "Closed". I cannot use the %complete column.

My initial thought is to have some lengthy looping "if-statement" but this does not seem scalable. Currently I have tried this approach (which is functional, but not ideal for scalability with new projects that may not have the exact same rows):

=IF([Status (1)]1 <> "Closed", "Pre-Sales / Sales", IF([Status (1)]6 <> "closed", "Planning", IF([Status (1)]15 <> "Closed", "Deployment - Design", IF([Status (1)]36 <> "Closed", "Deployment - Prep", IF([Status (1)]44 <> "Closed", "Deployment - Install", IF([Status (1)]47 <> "Closed", "Deployment - Validate", "Live"))))))

I am hoping for an option that is based on a "match" or lookup, as I know the "Task Name" column will have consistent naming (which is pulled from templates).

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!