Combine Child | Parent formulas into Column formula

I'm trying to redo our the Status formula for our project plan into a Column formula.

Parent row has this formula:

=IF(CONTAINS("Red", CHILDREN()), "Red", IF(CONTAINS("Yellow", CHILDREN()), "Yellow", IF(CONTAINS("Green", CHILDREN()), "Green", IF(COUNTIFS(CHILDREN(), "Blue") = COUNT(CHILDREN([Phase Status]@row)), "Blue", IF(COUNTIFS(CHILDREN(), ISBLANK(@cell)) = COUNT(CHILDREN([Phase Status]@row)), "")))))


Child row has this formula:

=IF([Phase Status]@row = "Complete", "Blue", IF(AND([Planned Start Date]@row <= TODAY(), [Phase Status]@row <> "In Progress"), "Red", IF(AND([Planned End Date]@row <= TODAY(), [Phase Status]@row <> "Complete"), "Red", IF([Phase Status]@row = "On Hold", "Yellow", IF([Phase Status]@row = "In Progress", "Green")))))

Below is a screenshot of my setup:


When I combine these two, every Status ball (using symbol column for Status) turns to blue. I know I'm missing something but I can't figure out what. Any help would be appreciated.

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 08/16/21 Answer ✓

    Hi @Peggy Parchert 

    Hope you are fine, please try the following formula:

    =if(Level@row=0,IF(CONTAINS("Red", CHILDREN()), "Red", IF(CONTAINS("Yellow", 
    CHILDREN()), "Yellow", IF(CONTAINS("Green", CHILDREN()), "Green", IF(COUNTIFS(CHILDREN(), "Blue")
    = COUNT(CHILDREN([Phase Status]@row)), "Blue", IF(COUNTIFS(CHILDREN(), ISBLANK(@cell))
    = COUNT(CHILDREN([Phase Status]@row)), ""))))),IF([Phase Status]@row = "Complete", "Blue",
    IF(AND([Planned Start Date]@row <= TODAY(), [Phase Status]@row <> "In Progress"),
    "Red", IF(AND([Planned End Date]@row <= TODAY(), [Phase Status]@row <> "Complete"), "Red",
    IF([Phase Status]@row = "On Hold", "Yellow", IF([Phase Status]@row = "In Progress", "Green"))))))
    

    PMP Certified

    [email protected]

    ☑️ 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 ✭✭✭✭✭✭
    edited 08/16/21 Answer ✓

    Hi @Peggy Parchert 

    Hope you are fine, please try the following formula:

    =if(Level@row=0,IF(CONTAINS("Red", CHILDREN()), "Red", IF(CONTAINS("Yellow", 
    CHILDREN()), "Yellow", IF(CONTAINS("Green", CHILDREN()), "Green", IF(COUNTIFS(CHILDREN(), "Blue")
    = COUNT(CHILDREN([Phase Status]@row)), "Blue", IF(COUNTIFS(CHILDREN(), ISBLANK(@cell))
    = COUNT(CHILDREN([Phase Status]@row)), ""))))),IF([Phase Status]@row = "Complete", "Blue",
    IF(AND([Planned Start Date]@row <= TODAY(), [Phase Status]@row <> "In Progress"),
    "Red", IF(AND([Planned End Date]@row <= TODAY(), [Phase Status]@row <> "Complete"), "Red",
    IF([Phase Status]@row = "On Hold", "Yellow", IF([Phase Status]@row = "In Progress", "Green"))))))
    

    PMP Certified

    [email protected]

    ☑️ 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"

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭

    @Bassam Khalil - Thank you! That worked. Appreciate the help.

    Peggy

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 08/16/21

    @Peggy Parchert

    Excellent, i will be happy to help you any time.

    PMP Certified

    [email protected]

    ☑️ 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"

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭

    @Bassam Khalil - could I trouble you again for some assistance?

    I'm attempting to do another column formula column for my At Risk column of our project plan

    Parent row formula:

    =IF(COUNTIF(CHILDREN(), 1) > 0, 1)

    Child row formula:

    =IF(AND([End Date]@row < TODAY(), NOT(Status@row = "Blue")), 1)

    Where my Status column is a symbol column (RYGB) and End Date is a Date column. I get an #UNPARSEBLE error message when I combine my two formulas.

    Combined formula

    =IF(COUNT(CHILDREN())>0, IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), 1), 1), IF(AND([End Date@row < TODAY(), NOT(Status@row = "Blue")), 1, 0))

    What am I missing?

    Thanks -Peggy

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Peggy Parchert

    No problem, please share the sheet with me as an admin.

    PMP Certified

    [email protected]

    ☑️ 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"

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭

    @Bassam Khalil - thank you. Invite sent.

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Peggy Parchert 

    Hope you are i fine, i fixed the formula for at risk column for parent and child please check it.

    PMP Certified

    [email protected]

    ☑️ 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"

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    For the first I would actually suggest not leveraging the Level column since not all parent rows are Level 0. the most consistent way I have found to apply a formula to parent rows vs child rows when parents can be on different levels is to use an IF/COUNT/CHILDREN similar to the formula in your second question.


    =IF(COUNT(CHILDREN([Task Name]@row)) = 0, child_row_formula, parent_row_formula)


    To avoid an error from having parenthesis or commas misplaced when combining the formulas, you can copy/paste while excluding the initial "=" sign.


    So if you have this:

    Parent row formula:

    =IF(COUNTIF(CHILDREN(), 1) > 0, 1)

    Child row formula:

    =IF(AND([End Date]@row < TODAY(), NOT(Status@row = "Blue")), 1)


    Then we would end up with something like this...

    =IF(COUNT(CHILDREN([Task Name]@row)) = 0, IF(AND([End Date]@row < TODAY(), NOT(Status@row = "Blue")), 1), IF(COUNTIF(CHILDREN(), 1) > 0, 1))

    .

    .

    That first formula would turn out more like this instead which will ensure that the parent row formula really is applied to ALL parent rows instead of just the very first row at the top.


    =IF(COUNT(CHILDREN([Task Name]@row)) = 0, IF([Phase Status]@row = "Complete", "Blue", IF(AND([Planned Start Date]@row <= TODAY(), [Phase Status]@row <> "In Progress"), "Red", IF(AND([Planned End Date]@row <= TODAY(), [Phase Status]@row <> "Complete"), "Red", IF([Phase Status]@row = "On Hold", "Yellow", IF([Phase Status]@row = "In Progress", "Green"))))), IF(CONTAINS("Red", CHILDREN()), "Red", IF(CONTAINS("Yellow", CHILDREN()), "Yellow", IF(CONTAINS("Green", CHILDREN()), "Green", IF(COUNTIFS(CHILDREN(), "Blue") = COUNT(CHILDREN([Phase Status]@row)), "Blue", IF(COUNTIFS(CHILDREN(), ISBLANK(@cell)) = COUNT(CHILDREN([Phase Status]@row)), ""))))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!