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
-
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.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
-
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.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 - Thank you! That worked. Appreciate the help.
Peggy
-
Excellent, i will be happy to help you any time.
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 - 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
-
No problem, please share the sheet with me as an admin.
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 - thank you. Invite sent.
-
Hope you are i fine, i fixed the formula for at risk column for parent and child please check it.
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"
-
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)), ""))))))
-
Thank you @Paul Newcome
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!