# Formula help

@Paul Newcome Reaching out for help again. How would you create an equation where the parent status is automatically updated when the child row statuses are changed. See attached picture.

When all of the "Actuals" are "Completed" then change the parent status to Completed, if any of the "Actuals" have started, changing the parent to "In Progress" if none of them have a status, then leave the cell blank. Does that make sense?

Thanks!!

• ✭✭✭✭✭✭

Is there a "face-palm" emoji? I was using "Complete" in my formula and you were using "Completed". It has been a while since I have fallen victim to the "Complete" vs "Completed" conundrum, so I guess I was due.

=IF(COUNT(CHILDREN()) <> 0, IF(CONTAINS("In Progress", CHILDREN()), "In Progress", IF(COUNTIFS(CHILDREN(), "Completed") = COUNT(CHILDREN([Panel #]@row)), "Completed", IF(CONTAINS("In Progress", DESCENDANTS()), "In Progress", IF(COUNTIFS(CHILDREN(), "Completed") = COUNTIFS(CHILDREN(Task@row), "Actual"), "Completed")))))

«1

• ✭✭✭✭

Sean,

I answered a question similar for Joe earlier with regard to status balls, the formula is similar for your questions

Instead of Counting the for Color, you will count the Child Tasks for Complete, In Progress, etc.

• @rgochee thanks for getting me started, I'm not the most competent person at formulas, can you see where I'm going wrong?

=IF(COUNTIF(CHILDREN(), "Completed") = COUNT(CHILDREN([Panel #]1)), "Completed", IF(AND(COUNTIF(CHILDREN(), "Completed") < COUNT(CHILDREN([Panel #]1)), COUNTIF(CHILDREN(), "Completed") > 0), “In Progress", “In Progress"))

• ✭✭✭✭

Here you go

=IF(COUNTIF(CHILDREN(), "Completed") = COUNT(CHILDREN([Panel #]1)), "Completed", IF(AND(COUNTIF(CHILDREN(), ="Completed") < COUNT(CHILDREN([Panel #]1)), COUNTIF(CHILDREN(), ="Completed") > 0), "In Progress", "In Progress"))

• Thanks, now it's not quite working the way I'd like, the "projected" row doesn't have a status, is that what's holding it up from changing to complete?

• Yes once you have status in the Row it will Change Accordingly

Note: Copy this formula to each of the Tasks that have Summary Tasks so that each of their progress will also roll up

• Here is a snapshoot- I changed the last part of the formula - so that if nothing is in the Status column under the summary task it will be Blank - and used the @row reference for you to easily copy/paste and add rows when needed

=IF(COUNTIF(CHILDREN(), "Completed") = COUNT(CHILDREN([Panel #]@row)), "Completed", IF(AND(COUNTIF(CHILDREN(), ="Completed") < COUNT(CHILDREN([Panel #]@row)), COUNTIF(CHILDREN(), ="Completed") > 0), "In Progress"))

Example below

• Sorry Sean,

I think I am following you now - you do not put a status in the Projected Task Status which should not be calculated in the formula for the status.

So the Main Task (i.e Pack #LVL7) will have one formula removing the Projected Summary Tasks from the equation

=IF(COUNTIF(CHILDREN(), "Completed") = (COUNT(CHILDREN([Panel #]@row)) - COUNTIF(Task:Task, ="Projected")), "Completed", IF(COUNTIF(CHILDREN(), >"") > 0, "In Progress"))

The Sub tasks of the Main Task - (i.e 708 "Actual") will have this formula

=IF(COUNTIF(CHILDREN(), "Completed") = COUNT(CHILDREN([Panel #]@row)), "Completed", IF(COUNTIF(CHILDREN(), >"") > 0, "In Progress"))

This will then monitor the Main Task Progress and each of the Summary Tasks independently. Let me know if this functions as you want it to. Rob

• ✭✭✭✭✭✭

You could try something like this.

For the Parent Rows that contain the word "Actual":

=IF(CONTAINS("In Progress", CHILDREN()), "In Progress", IF(COUNTIFS(CHILDREN(), "Complete") = COUNT(CHILDREN([Panel #]@row), "Complete"))

For the Parent Row that contains the Pack #:

=IF(CONTAINS("In Progress", DESCENDANTS()), "In Progress", IF(COUNTIFS(CHILDREN(), "Complete") = COUNTIFS(CHILDREN(Task@row), "Actual"), "Complete"))

We want the above to only run on rows that have children (this will exclude the "Projected" rows):

=IF(COUNT(CHILDREN()) <> 0, ........................................

Roll all three of these into a combined formula to allow for dragfilling, and you would end up with something like this:

IF(COUNT(CHILDREN()) <> 0, IF(CONTAINS("In Progress", CHILDREN()), "In Progress", IF(COUNTIFS(CHILDREN(), "Complete") = COUNT(CHILDREN([Panel #]@row), "Complete")), IF(CONTAINS("In Progress", DESCENDANTS()), "In Progress", IF(COUNTIFS(CHILDREN(), "Complete") = COUNTIFS(CHILDREN(Task@row), "Actual"), "Complete"))

• I've tried to adjust to see where it's not working, but this is getting well beyond my ability. Here are some screen shots

• ✭✭✭✭✭✭

See how the quotes before each "In Progress" are slanted? Those are "smart quotes", and those will break a formula in an instant. Smart quotes come from programs such as Microsoft Word. I'm not sure what the other ones are called ("not so smart quotes"?), but you want the ones that are straight up and down. Those come from directly within Smartsheet and here in the Community as well as certain text editors such as Notepad.

Remove those two particular quotes and re-enter them in Smartsheet and see if that gets rid of the error.

• oh man, good eye, so I made the change, but it recognized it as "In Progress" but should be completed. Thanks for your time helping me BTW, I'm learning a ton.

=IF(COUNTIF(CHILDREN(), "Completed") = COUNT(CHILDREN([Panel #]1)), "Completed", IF(AND(COUNTIF(CHILDREN(), "Completed") < COUNT(CHILDREN([Panel #]1)), COUNTIF(CHILDREN(), "Completed") > 0), "In Progress", "In Progress"))

• ✭✭✭✭✭✭

That is why I used this:

COUNT(CHILDREN([Panel #]1))

In your formula, you are counting all child rows including the Projected Rows.

Try dropping this in to see how it does. I made a few small tweaks to the previous formula (my parenthesis needed adjusting).

IF(COUNT(CHILDREN()) <> 0, IF(CONTAINS("In Progress", CHILDREN()), "In Progress", IF(COUNTIFS(CHILDREN(), "Complete") = COUNT(CHILDREN([Panel #]@row)), "Complete", IF(CONTAINS("In Progress", DESCENDANTS()), "In Progress", IF(COUNTIFS(CHILDREN(), "Complete") = COUNTIFS(CHILDREN(Task@row), "Actual"), "Complete")))))

• That resulted in a blank cell

• ✭✭✭✭✭✭

Are you able to provide another screenshot but with the parent rows expanded?

What are the chances you could save the sheet as new, remove sensitive/confidential data, publish as "Edit by Anyone", then provide that link here? Being able to work in the sheet (or at least an inactive version of it) would be very helpful.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!