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