Making parent complete if children complete BY REFERENCING OTHER COLUMNS

Brandon Newberg

Hi. I know similar questions have been asked, but none seem to reference other columns, which is the key problem for me.

Problem: In my sheet, the Health column symbol changes color based on End Date column UNLESS the Progress column is marked "Complete", in which case health turns green. However, as you can see in the screenshot below, this leaves the parent row red unless the parent row is also independently marked complete.

Desired Outcome: I would like the parent row to *automatically* turn green if all of the children are marked complete. I've tried using the CHILDREN function for this but can't get it to work referencing child cells in other columns. I can only get it to work referencing child cells in the same column.

Here is the formula I currently have for the health column:

=IF(ISBLANK([End Date]@row), " ", (IF(AND([End Date]@row <= TODAY(), NOT(Progress@row = "Complete")), "Red", (IF(AND([End Date]@row <= TODAY(7), NOT(Progress@row = "Complete")), "Yellow", (IF(AND([Start Date]@row > TODAY(), NOT(Progress@row = "Complete")), "Gray", "Green")))))))

Thanks in advance for any help!

Victoria_Indimar

Hi Brandon,

You can reference the children in a different cell by doing CHILDREN(Progress@row). If you are wanting to determine value of Health based on the Progress of the children all being Complete, I've used a formula like this:

IF((COUNTIF(CHILDREN(Progress@row), ="Complete")) = COUNT(CHILDREN(Progress@row)), "Green", "Red")

It checks how many children are Complete. If that matches the number of children, that means all children are complete, then it should be green too -- if not, then it can go through the rest of your formula to determine what it should be.

Hope this helps!