Combine Child | Parent formulas into Column formula

Peggy Parchert

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.

Bassam Khalil

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

Bassam Khalil

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

Peggy Parchert

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

Peggy

Bassam Khalil

@Peggy Parchert

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

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

@Peggy Parchert

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

Peggy Parchert

@Bassam Khalil - thank you. Invite sent.

Bassam Khalil

@Peggy Parchert 

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

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

Peggy Parchert

Thank you @Paul Newcome

Paul Newcome

Happy to help. 👍️