Using formulas to determinate the status of a set of tasks

Miguel Bravo

Hi community, 

I'm new on Smartsheet and I'm trying to make a formula to automatically calculate the status of a set of tasks (or subtasks).

Let me explain my situation: I've created a main task, lets say "Project1" and it contains some subtasks as children. Next to this first column, I've got a column for the status (its type is dropdown and I've defined it with a set of values ["Waiting", "Started", "Ongoing", "Finished", "Closed"].

My query is if I can automatically set the value of "Project1" status based on the status of the childrens. As I first approach, I would like to set the status as "Ongoing" if any of the childre are in "Ongoing", and "Closed" if all the children are "Closed".

I'm trying with any of the combinations in the template, but none of them uses Dropdown list values, and any of the following formulas return error messages:

=if([Project1]1=Closed) --> #UNPARSEABLE

=if[Project1]1='Closed) --> #UNPARSEABLE

=if[Project1]1="Closed") --> #INCORRECT ARGUMENT SET

 

Can you please help me?

Thanks in advance

Paul Newcome

=IF(COUNTIF(CHILDREN(), "Ongoing") > 0, "Ongoing", IF(COUNTIF(CHILDREN(), "Closed") = COUNT(CHILDREN()), "Closed"))

 

This will take care of your Ongoing and Closed statuses. Basically what it does is:

 

It counts the children if the status is Ongoing. If that count is 1 or more (greater than 0), then it will set the status as Ongoing. If the count of Ongoing is 0, then it will count the number of Closed statuses. If that count matches the count of the children (meaning all are closed), then it will set the status as Closed.

 

To be able to fully automate though, you are going to want to write in conditions for all of your statuses. If you only automate part of it, and do the other parts manually, the manual entry will override and ERASE the formula.