Get Most Updated Task Status From Child

Paul Newcome

Happy to help! 

priyanka.gadiyaram.consultant88191

Hi All,

I am pretty new to Smartsheets and trying to understand my way around it. Mine is a similar question except that I need the parent row to update its status when/if a child row's status is updated.

Also, I do not know where to write "IF" formula in the sheet. Do I create a new blank column next to Status and then insert the formula? I tried dragging a sample formula to underlying rows and it did nothing. Here is a screenshot of my schedule. Thanks in advance!

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"

P.S: I currently update all parent rows manually. I have multiple mega-parent rows which break into multiple children rows and further broken down into sub-tasks and children rows.

priyanka.gadiyaram.consultant88191

Hi,

I have a similar questions except that my column does not have NA. All I have are Complete, Not Started & In progress status. I have multiple parent rows, multiple children rows and sub-tasks within them. I am trying to update relevant parent row statuses whenever I modify a child row. I am pretty new to Smartsheet and trying to learn about customizing. I tried the formula posted above but the status of a parent row shows NA for sub tasks that are in progress and/or not started. Thanks in advance!

Paul Newcome

Will you have blanks that need to be accounted for?

 

Are you able to post some screenshots?

priyanka.gadiyaram.consultant88191

No, I won't have blank fields in "status" column. All I would have are "not started", "in progress" and "complete". I tried using the formula above but it turned the status to NA. I also tried posting a screenshot but I see a message that it would be reviewed by the admin and would be posted after their approval. Thank you!

Paul Newcome

Ok. Before I throw a formula out there for you to try out, let's make sure we have your specific criteria correct...

 

If all are "Not Started" then parent is "Not Started".

If all are "Complete" then parent is "Complete".

All other combinations would yield "In Progress".

 

Is this correct?

priyanka.gadiyaram.consultant88191

Yes, that is correct. I would have the tasks marked ONLY in one of those statuses. Only if all child tasks are complete, parent is complete. Else they will have the criteria you mentioned. I am trying to keep it simple and not complicate the schedule. 

Thank you so much!

Paul Newcome

Ok. Give this a go...

 

=IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), "Not Started"), "Not Started", IF(COUNT(CHILDREN()) = COUNTIFS(CHILDREN(), "Complete"), "Complete", "In Progress"))

priyanka.gadiyaram.consultant88191

Thank you Paul. But I am afraid it did not work. For instance, I had 3 child tasks that had statuses 1 in progress and 2 not started. the parent task showed not started. Should I use it only for one parent task and not sub-parent tasks within it? Also, I entered it on blank column and called it status. Do I have to enter it in some other field or column or row?

Paul Newcome

It should be in the same status column as the children statuses.

 

That's odd that it isn't working. Can you post a screenshot with the current non-working formula?

Lindseyh111241

@Craig. This is AMAZING!!! Thank you