Updating a Parent Row Based on Children's Status

Tony Fronza

Hello!

We are trying to create a formula to accommodate the following logic: IF any [RFP Status] of children HAS In Progress or Canceled return the value "In Progress", IF ALL [RFP Status] of children HAS Complete or Canceled return the value "Complete", Otherwise, Not Started.

I generated the formula below using AI, however, it's returning a value of Complete when some tasks are still In Progress. Please help :)

=IF(OR(CONTAINS("In Progress", CHILDREN([RFP Status]@row)), CONTAINS("Canceled", CHILDREN([RFP Status]@row))), "In Progress", IF(AND(COUNTIF(CHILDREN([RFP Status]@row), "Complete") + COUNTIF(CHILDREN([RFP Status]@row), "Canceled") = COUNT(CHILDREN([RFP Status]@row)), COUNTIF(CHILDREN([RFP Status]@row), "Complete") > 0), "Complete", ""))

DKazatsky2

Hi @Tony Fronza,

See if this works out better for you.

=IF(CONTAINS("In Progress", CHILDREN()), "In Progress", IF(COUNTIF(CHILDREN(), "Complete") + COUNTIF(CHILDREN(), "Canceled") = COUNT(CHILDREN()), "Complete", "Not Started"))

Notes:

• This can only be put into the parent row, cannot be a column formula
• If any of the [RFP Status]@row cells are blank, this will not work as the COUNT formula only includes cells with values

Hope this helps,

Dave

DKazatsky2

Hi @Tony Fronza,

See if this works out better for you.

=IF(CONTAINS("In Progress", CHILDREN()), "In Progress", IF(COUNTIF(CHILDREN(), "Complete") + COUNTIF(CHILDREN(), "Canceled") = COUNT(CHILDREN()), "Complete", "Not Started"))

Notes:

• This can only be put into the parent row, cannot be a column formula
• If any of the [RFP Status]@row cells are blank, this will not work as the COUNT formula only includes cells with values

Hope this helps,

Dave

Tony Fronza

@DKazatsky2 Thank you! This works for everything except for Not Started. The only time Not Started should appear is if ALL child rows are Not Started; otherwise, it should be in Progress. Any thoughts?

Tony Fronza

@DKazatsky2 never mind! There was an issue with the sheet's formatting. It's working now! Thanks again.