If statements with multiple conditions

Carrie Fouche

Hi,

Hoping someone can help with a formula I am struggling to get to work for a specific scenario. I have a "phase column" in my project plan which looks at various parent rows for my project phases, i.e Discovery, Design, Build, Implement, Benefits realisation. The formula assesses the status of these phases, so I can indicate at a high level what phase the project is on. Below is the statement I use, which works perfectly when each phase is done in chronological order. 

=IF(Status2 = "In Progress", "Discovery", IF(Status15 = "In Progress", "Design", IF(Status116 = "In Progress", "Build", IF(Status159 = "In Progress", "Implement", IF(Status194 = "In Progress", "Benefits Realisation", IF(Status93 = "In Progress", "All Phases", IF(AND(Status2 = "Complete", Status15 = "Complete", Status116 = "Complete", Status159 = "Complete", Status194 = "Complete", Status93 = "Complete"), "Closed", "Not Started")))))))

 

The issue I have is where there is more than one phases  "In Progress" as this then throws out the IMPARSEABLE error. I would like the formula to read the status as above and apply the phase based on the earliest phase still showing in progress, i.e if the status is "In Progress for both Discovery and Build, then it should show the phase as "Discovery". Can anyone help please?

 

Thanks

Carrie

J. Craig Williams

That sounds like a similar problem as I describe here:

http://ronin-global.com/2018/07/18/finding-next-task-in-smartsheet/

Does that help?

Since you are (likely) using parent rows, I would not code it the way you describe.

You'll need to expand on my 'next' post with ANCESTORS() and COLLECT, I suspect.

Craig

 

Paul Newcome

I was JUST digging through old posts looking for this very link, sitting here thinking to myself... "I know Craig had a solution to this. Now where did it go?"

 

Using ANCESTORS() as a criteria to determine which level of hierarchy to look at seems like it would be a great way to go. I've been finding more and more uses for it myself these past couple of months.