Parent cell update based on change in children cell

Options

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


Best Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓
    Options

    @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"), "")

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓
    Options

    @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". 

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    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:


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Nethra Kumar
    Options

    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
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓
    Options

    @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"), "")

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Nethra Kumar
    Options

    thank you, Bassam. This works perfectly

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓
    Options

    @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". 

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"