automation - mark Main Task 100% complete and Status=Complete when all sub tasks are complete

Michael Watson

Hi, i'm a newbie and have a question about Smartsheet.

I was wondering if Smartsheet can do the following: I would like for the parent task to be marked 100% complete and Status=Complete when the sub-tasks are 100% complete and Status=Complete. For example, I have a task call "Deploy Server" and there are 3 sub tasks underneath it ("Task 1,"Task 2",Task 3"). When the last sub-task has been marked 100% and Status=Complete, the Main task called "Deploy Server" is marked 100% and Status=Complete.

Is there a way to do that?

Thanks in advance.

Paul Newcome

Ok. Try something like this in the Status Column...

=IF([% Complete]@row = 1, "Complete")

If you wanted to go ahead and show "Not Started" for 0% and "In Progress for 1% - 99%, you could use something like this...

=IF([% Complete]@row = 1, "Complete", IF([% Complete]@row = 0, "Not Started", "In Progress"))

Paul Newcome

How are you entering the %Complete?

Michael Watson

Hi Paul, thanks for responding. For the sub-tasks, I enter "100" and I see Smartsheet enters "100%" for the value. Also, I enter "Complete" for Status field.

Paul Newcome

Ok. So in the % Complete parent row, you would enter something along the lines of

=AVG(CHILDREN())

For the Status column, what are your other statuses? We would have to fully automate these because if you were to manually change the status, it will override and delete any formula that is in the cell.

Michael Watson

Thanks Paul. For Status, I have three values (Not started, In Progress, and Complete)

Michael Watson

Paul, I realize you reference the use of Formulas in Smartsheet. I've used Formulas in Excel but have not reviewed them for Smartsheet (although the Smartsheet and Excel formulas looks similar).

I will take some time this week to educate myself on formulas. Thanks for the response.

Paul Newcome

The formulas between Excel and Smartsheet are similar in a lot of aspects but also vastly different in others.

Your three values in your Status column... How do you determine when to manually update them? It could possibly be very easy to automate these.

Michael Watson

thanks Paul. I'm only using Status = Complete to show that when a task is complete. Not using Not started and may start using In Progress in the near future.

Paul Newcome

Ok. Try something like this in the Status Column...

=IF([% Complete]@row = 1, "Complete")

If you wanted to go ahead and show "Not Started" for 0% and "In Progress for 1% - 99%, you could use something like this...

=IF([% Complete]@row = 1, "Complete", IF([% Complete]@row = 0, "Not Started", "In Progress"))

Michael Watson

Yes, that works. Thanks for sharing this.

Paul Newcome

Happy to help! 👍️

Lubov Batton

@Paul Newcome , thanks for the comments above. I am trying to accomplish automation of my % Complete column and was able to apply your complete=100% formula, but not the extended one for all statuses. Maybe a dumb question, but I do not understand, why do we have to associate 100% completeness with 1 in this formula -

=IF([% Complete]@row = 1, "Complete")

Therefore, I do not understand the following part either - IF([% Complete]@row = 0, "Not Started", "In Progress")

Why 0, and why both NOT STARTED and IN PROGRESS equal 0?

Thank you

Paul Newcome

@Lubov Batton

Smartsheet reads percentages as a part of a whole. So 1 of 1 whole is 100%, .5 is 50%, .25 so 25%, so on and so forth.

The second part of your question is actually a two part explanation.

IF([% Complete]@row = 0, "Not Started", "In Progress")

IF([% Complete]@row = 0, "Not Started"

This part says that if the % Complete equals zero, then it is not started.

, "In Progress")

This part when added to the entire formula with both of the previous sections combined (the 1 and the 0), says that anything that does not meet the criteria for the previous two outputs is "In Progress".

So the entire formula:

=IF([% Complete]@row = 1, "Complete", IF([% Complete]@row = 0, "Not Started", "In Progress"))

Reads as:

If the % Complete equals 1 (100%), then output "Complete".

If the % Complete equals 0 (0%), then output "Not Started".

If neither are true (meaning it is greater than zero but less than 1 (or 100%), then output "In Progress".

CJU

Hi all, for this to work do you have 2 columns? That is, you have a column with a drop down, e.g. Not started, In Progress, Complete; and then the formula for the main task is in another column?

Paul Newcome

@CJU I'm not sure I understand what you are asking. What do you mean by "then the formula for the main task is in another column"?

Cyndi

@Paul Newcome - I am searching for something similar and came across this - hope you are able to provide additional direction. But shouldn't there be a way to automate project plans to have a task reflect based on the status's of it's subtasks?

Parent Row and Child Rows have Status of Complete , Not Started, In Process.

I'm good at if one child row is complete, then complete ...but what about this?

If - Child1, Child2, Child3 are all Complete, then "Complete" OR if Child1,Child2, Child3 are all Not Started, then "Not Started" HOWEVER if any child row = In Process, then Parent is "In Process" :)