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

https://community.smartsheet.com/discussion/comment/310532#Comment_310532

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 :(