Formula help

Sean Murphy

@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!!

Paul Newcome

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")))))

rgochee

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.

Sean Murphy

@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"))

rgochee

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"))

Sean Murphy

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?

Robert Gochee

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

Robert Gochee

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

Robert Gochee

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

Paul Newcome

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"))

Sean Murphy

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

Paul Newcome

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.

Sean Murphy

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"))

Paul Newcome

That is why I used this:

COUNTIFS(CHILDREN(Task@row), "Actual")

Instead of

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")))))

Sean Murphy

That resulted in a blank cell

Paul Newcome

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.

Sean Murphy

https://app.smartsheet.com/b/publish?EQBCT=f2ea58438f984c2e9b78128718f4fa0a let me know if this doesn't work