Using formulas to determinate the status of a set of tasks

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

Find more posts tagged with

Formulas

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?