Formula to update status on a parent row based on child row with OR statement

Paul Newcome

@JennV Why exactly do you have 3 = "Complete", 3 = "Cancelled"? If those are not part of the text within the cell then you would need to remove those bits.

JennV

@Paul Newcome Not sure why the cell ref changed. Maybe I shouldn't work at midnight anymore. lol Thanks for catching that.

Paul Newcome

@JennV Happy to help. 👍️

Dawn_Cox

Eric Leon

Nov 10, 2021

Could you please help me @Frank Falco.

All balls will start off Red, and team will manually change the Children to Green balls once task is complete. The hopeful outcome once all Children balls are complete, the Parent ball will automatically change to Green. Is there a formula you can do for the balls?

https://us.v-cdn.net/6031209/uploads/UXAH2LNEYE72/smartsheet-example.jpg

This is the problem I'm trying to figure out - has anyone found a solution to do what Eric asked about?

CBRNDLREZI

Hi, I am trying to understand where / how I put in reference to my column names that have the hierarchy and the status info (i.e. Task A, Task A.1, Task A1.2.....Completed, In Progress, etc....) the the formula below:

=IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "Cancelled") = COUNT(CHILDREN()), "Cancelled", IF(COUNTIFS(CHILDREN(), "On Hold") = COUNT(CHILDREN()), "On Hold", IF(COUNTIFS(CHILDREN(), OR(3 = "Complete", 3 = "Cancelled")) = COUNT(CHILDREN()), "Complete", "In Progress")))

For discussion, let's say my Task column is called "Tasks" and my Status column is called "Status".

Paul Newcome

@CBRNDLREZI The formula you have in your post would go in the parent rows of the Status column.

CBRNDLREZI

Thank you for responding @Paul Newcome . I tried pasting into my status column, but all cells show "Not Started". I thought that I would have to add in reference to my specific columns like ( [Task Name]@row & [Status]@row ) in the formula itself. Is this not the case? The part I wasn't able to understand was where to put them in the formula. Can you lend some additional insight?

Example: =IF(COUNTIFS(CHILDREN([Task Name]@row), "Not Started") = COU.....

Paul Newcome

@CBRNDLREZI Are you able to provide a screenshot for reference? You do not need to reference any columns if you are putting the formula in the parent rows of the Status column.

CBRNDLREZI

@Paul Newcome , Sure. here is a screen shot. The cells in yellow are ones that I pasted the formula into. The other values have been entered manually. (Note: Green rows are just a result of conditional formatting) Thanks for your continued help...

I found my error. Small typo :(