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!!
Best Answer

Is there a "facepalm" 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")))))
Answers

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

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.

https://app.smartsheet.com/b/publish?EQBCT=f2ea58438f984c2e9b78128718f4fa0a let me know if this doesn't work
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!