Apply a formula in a Status column only for the Parent rows.

MargaritaSarmiento

I've been struggling with this a little bit.

I want to pick the status with the dropdown options only for the rows without children and pick the status of the parent rows based on that selection.

I have this formula: =IF(Ancestors@row = "True", IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIF(CHILDREN(), "In Process") = COUNT(CHILDREN()), "In Process", IF(COUNTIF(CHILDREN(), "Completed") = COUNT(CHILDREN()), "Completed", IF(COUNTIF(CHILDREN(), "Not Required") = COUNT(CHILDREN()), "Not Required", IF(COUNTIF(CHILDREN(), "Upcoming") = COUNT(CHILDREN()), "Upcoming", IF(COUNTIF(CHILDREN(), "Completed") + COUNTIF(CHILDREN(), "Not started") = COUNT(CHILDREN()), "In Process", IF(COUNTIF(CHILDREN(), "Completed") + COUNTIF(CHILDREN(), "Not required") = COUNT(CHILDREN()), "Completed", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Process", "In Process")))))))))..

However, if I convert this to a column formula, I am not able to display the dropdown menu for the children rows.

In summary, rows that are Parent 1 should have the formula, rows that are Parent 2 should have the formula ONLY if they have children

I also tried using this formula with AND OR conditions, but I had a syntax error:

=IF(OR(AND(Parent@row = "2", Children@row > 0), Parent@row = "2"), IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIF(CHILDREN(), "In Process") = COUNT(CHILDREN()), "In Process", IF(COUNTIF(CHILDREN(), "Completed") = COUNT(CHILDREN()), "Completed", IF(COUNTIF(CHILDREN(), "Not Required") = COUNT(CHILDREN()), "Not Required", IF(COUNTIF(CHILDREN(), "Upcoming") = COUNT(CHILDREN()), "Upcoming", IF(COUNTIF(CHILDREN(), "Completed") + COUNTIF(CHILDREN(), "Not started") = COUNT(CHILDREN()), "In Process", IF(COUNTIF(CHILDREN(), "Completed") + COUNTIF(CHILDREN(), "Not required") = COUNT(CHILDREN()), "Completed", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Process", "In Process")))))))))

Paul Newcome

You would have to manually enter the formula into each of the parent rows.

MargaritaSarmiento

😢 Thank you