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

E. Robert Shanfeld

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).

Brett Smith

Hey there. Checkout the INDEX(COLLECT... example here INDEX Function | Smartsheet Learning Center

If you change:

• Transaction Total to your Task Name column
• 'In Stock?' column with your Status column and
• true to ="Open"...

Should work provided your rows are always in the order listed.

Brett Smith

Hey there. Checkout the INDEX(COLLECT... example here INDEX Function | Smartsheet Learning Center

If you change:

• Transaction Total to your Task Name column
• 'In Stock?' column with your Status column and
• true to ="Open"...

Should work provided your rows are always in the order listed.

E. Robert Shanfeld

THAT WORKS! Thank you