Get Most Updated Task Status From Child

Paul's solution does not scale. 

ASSUMPTION based on question phrasing: Sub Tasks are completed in order (this may not matter, but it is an assumption nonetheless). You aren't looking for the first instance of one of the statuses and then if not found the next. 

Try this:

=IFERROR(INDEX(CHILDREN(), MIN(IFERROR(MATCH("Cancelled", CHILDREN(), 0), 5001), IFERROR(MATCH("In Progress", CHILDREN(), 0), 5001), IFERROR(MATCH("Not Started", CHILDREN(), 0), 5001), IFERROR(MATCH("Waiting", CHILDREN(), 0), 5001))), "Complete")

Further notes:

5001 is returned for each unmatched type because that is beyond the row limit and won't be returned by a valid match.

If 5001 IS returned, then no valid pattern was found. This is either because:

a. All children are complete

b. Some children are mis-typed (data validation might be recommended here)

c. I don't search for blanks (but could based on recent changes to the MATCH return value)

d. 5001 is an invalid input to the INDEX() function, which throws an error. I catch the error and return "Complete" (see a and b). That may mask a different error, and to be honest, I'm not completely comfortable with this, but to fix it (I think) would require something like =IF(a value that has a lot of characters to determine it, return true result for the value that has to be recalculated using those same set of characters, return false result that may also take all those characters to determine what it is) *

* put another way, Smartsheet does not have a mean to store a temporary value except by adding another formula in a cell, which usually means adding a column to store it in. I'm not opposed to that, but avoid it if possible. Sometimes, there is an unused column on the parent row where that temp value can be stored, but finding ones that aren't shown for the casual user is sometimes difficult.

There might be a way to make this formula more compact but I have a solution so I am moving on for now.

Craig

Paul,

Thanks.

If you don't mind, I'd like to say something else about your solution. 

=IF(OR(Status2 = "Not Started", Status2 = "Waiting", Status2 = "In Progress"), Status2, IF(AND(Status2 = "Complete", OR(Status3 = "Not Started", Status3 = "Waiting", Status3 = "In Progress")), Status3, IF(AND(Status3 = "Complete", OR(Status4 = "Not Started", Status4 = "Waiting", Status4 = "In Progress")), Status4)))

First, you missed the 5th status (Cancelled). That's minor since we won't be using it, but is worth getting out of the way.

More importantly, sometimes, you need to think backwards. The first level of the Nest IF is

IF(OR(any non-complete status for row 2),that status, keep going)

the next level (keep going) is

IF(AND(row 2 is complete),OR(any non-complete status for row 2)),that status, keep going)

and so forth.

If we flip our thinking, to this

IF(status2 = complete, keep going, that status)

then when we get to the next level we already know that the previous row IS complete, so the following ones become

IF(status3 = complete, keep going, that status)

While this code is smaller (and thus less of a CPU burden), it also has the added advantage that if the user decides to add 1 or more statuses, or change their names, the formula still works.

I hope this helps.

Craig

Here is a longer explanation:

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

Craig

Awesome thread!

J. Craig, I also read your blog post on the Ronin Global site -- thanks for diving into more detail. I also posted this question there, but wanted to add it here in the hope that you can help and more people can benefit from your clever solutions; here's my situation:

I have a similar need but also share J. Craig's spidey-sense that the first non-completed child task becomes the status for the parent task.  

In my example we have the same parent task to one or more child tasks structure as described by the OP. In our case, the status options for the children are:

• Completed
• In Progress
• Not Started
• NA

Assuming there were multiple sub-tasks with varying statuses, how could you write a formula that made the parent status:

1. "Completed" if all children were set to "Completed"
2. "Not Started" if all children were set to "Not Started" 
3. "In Progress" if *any* of the children were set to "In Progress".
4. "In Progress" if, when more than child exists, one is set to "Completed" and one to "Not Started" but none are set to "In Progress"

Thanks!!

-Chris

Thanks, Paul!!

Let me test drive this!

-Chris

Thanks Chris.

I responded to your post on my website, referencing back to here.

It appears you and Paul nearly have the solution so I'll leave Paul to finish. You are in good hands.

Thanks for the kind words

Craig

Craig,

Thanks!