Making parent complete if children complete BY REFERENCING OTHER COLUMNS
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!
Help Article Resources
Check out the Formula Handbook template!