Parent cell update based on change in children cell

Nethra Kumar

Hi Guys,

Is there a formula to update the status of a parent cell based on status update of children cell. The column type is single select drop down list. Attaching a screenshot for your reference

Bassam Khalil

@Nethra Kumar 

The following formula will do that for you ( update the parent cell as 'Completed' only after all children cell are marked completed)

=IFERROR(IF(COUNTIFS(CHILDREN(), "Completed") = COUNTM(CHILDREN()), "Completed"), "")

Bassam Khalil

@Nethra Kumar 

You are welcome and I will be happy to help you any time.

Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful". 

Bassam Khalil

Hi @Nethra Kumar

Hope you are fien, please try the following formula in parent level.

=IFERROR(IF(COUNTIFS(CHILDREN(), "Completed") > 0.5 * COUNTM(CHILDREN()), "Completed", IF(COUNTIFS(CHILDREN(), "In Progress") > 0.5 * COUNTM(CHILDREN()), "In Progress", IF(COUNTIFS(CHILDREN(), "Not Started") > 0.5 * COUNTM(CHILDREN()), "Not Started", ""))), "")

the following screenshot shows the result:

Nethra Kumar

Thanks for this Bassam.

But, I am looking to update the parent cell as 'Completed' only after all children cell are marked completed. Can you help me with it

Bassam Khalil

@Nethra Kumar 

The following formula will do that for you ( update the parent cell as 'Completed' only after all children cell are marked completed)

=IFERROR(IF(COUNTIFS(CHILDREN(), "Completed") = COUNTM(CHILDREN()), "Completed"), "")

Nethra Kumar

thank you, Bassam. This works perfectly

Bassam Khalil

@Nethra Kumar 

You are welcome and I will be happy to help you any time.

Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful".