PARENT CHILDREN FORMULA

C. Srinidhi Ragunathan

Greetings,

I need to formulate an equation in a parent cell. 

The children cells indicate certain conditions for each task(row). These conditions are as follows:  component received, awaiting component, not required.

I need a function such that, if all children cell indicate "component received", then the parent cell must automatically indicate the same.

Even if one child cell indicates otherwise, the parent cell must indicate "awaiting component"

I tried to use the following: =IF(([PRODUCT STATUS]x:[PRODUCT STATUS]y) = "RECEIVED COMPONENT", "RECEIVED COMPONENT", "AWAITING COMPONENT"). 

But it doesn't work.

 

Can anyone suggest changes to the problem?

 

Thank you

Paul Newcome

Give this a go...

 

=IF(COUNTIFS(CHILDREN()), "Awaiting Component") > 0, "Awaiting Component", IF(COUNTIFS(CHILDREN()), OR(@cell = "Received Component", @cell = "Not Required")) = COUNT(CHILDREN()), "Received Component"))

 

This says that if Awaiting is found anywhere, then it will display Awaiting. If all children are either received or not required, then it will show received.

C. Srinidhi Ragunathan

Thank you

Paul Newcome

Happy to help! 

Thiago Castro

Hi Paul

I have a similar issue, where I have more than one level.

I have Phase, Milestone, Task and sometimes sub-tasks.

I'm trying to achieve a health formula to show:

if there's any Task or subtask in Red then automatically the milestone should change to Yellow

If any milestone is yellow the phase should show as yellow if there's any milestone red then the phase should.

That way I can see that if on that phase anything is either yellow or red then it needs my attention.

Would you use the same logic? and what's your recommended formula for it?

Thanks in advance.

Paul Newcome

Are you able to spell out in detail every scenario you want covered and it's outcome?

 

 

Thiago Castro

Hi Paul, sure...

if there's any Task or subtask in Red then automatically the Milestone should change to Yellow

If any Milestone is yellow the Phase should show as Yellow

if there's any Milestone Red then the phase should show as Red.

That way I can see that if on that phase anything is either yellow or red then it needs my attention.

I'm trying to get a health check:

Green - Completed

Blue - In Progress

Yellow - Attention

Red - Delayed

At the moment this is the formula I'm using but it ONLY works on 1 parent up (Task red then Milestone becomes Red but NOT the Phase)

Schedule Hierarchy is:

Phase → Milestone → Task → Subtask

=IF([Start Date]@row= "", "", IF([Start Date]@row > TODAY(), "Blue", IF(AND([% Complete]@row < 1, [End Date]@row < TODAY()), "Red", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Yellow", "Green"))))

Example1:

IF I have 3 layers (Phase → Milestone → Task) 

Phase 1 (Green) ← I would like this to show Yellow as at least 1 milestone is yellow

Milestone 1 (Yellow)

Task 1 (Green)

Task 2 (Green)

Task 3 (Red)

Milestone 2 (Green)

Task 1 (Green)

Task 2 (Green)

Task 3 (Green)

I'm happy to hear your suggestion in how Health should be calculated and the rationale in case I'm looking into it the wrong way.

Thanks

Paul Newcome

Try something like this...

 

=IF(CONTAINS("Red", CHILDREN()), "Red", IF(OR(CONTAINS("Red", DESCENDANTS()), CONTAINS("Yellow", CHILDREN())), "Yellow", IF(CONTAINS("Blue", CHILDREN()), "Blue", "Green")))

Thiago Castro

Hi Paul,

 

It works if manually selected. The challenge now is how to automate this to complete automatically combining both formulas I'd say.

 

=IF([Start Date]@row = "", "", IF([Start Date]@row > TODAY(), "Blue", IF(AND([% Complete]@row < 1, [End Date]@row < TODAY()), "Red", IF(COUNTIFS(CHILDREN(), "Red") > 0, "Yellow", "Green"))))

=IF(CONTAINS("Red", CHILDREN()), "Red", IF(OR(CONTAINS("Red", DESCENDANTS()), CONTAINS("Yellow", CHILDREN())), "Yellow", IF(CONTAINS("Blue", CHILDREN()), "Blue", "Green")))

As I will be dragging this down on health column to cover everything (phase, milestone and tasks).

 

Thoughts?

 

Thanks in advance

 

Paul Newcome

This is actually pretty straightforward believe it or not. All we need to do is nest them in an IF statement that checks to see whether or not the row is a parent row.

 

=IF(COUNT(CHILDREN()) > 0, 

 

This basically says "If this is a parent row"

.

=IF(COUNT(CHILDREN()) > 0, formula for parent rows, formula for child rows)

.

Insert the parent row formula into the "if true" portion and the child row formula into the "if false" portion of the IF statement, and you should be squared away.

Thiago Castro

Hi Paul

I end up creating the following formula

=IF([Start Date]@row = "", "", IF(AND([Start Date]@row > TODAY(), [% Complete]@row = 0), "Blue", IF(AND([% Complete]@row < 1, [End Date]@row < TODAY()), "Red", IF(OR(CONTAINS("Red", DESCENDANTS()), CONTAINS("Yellow", CHILDREN())), "Yellow", IF(CONTAINS("Green", DESCENDANTS()), "Green", "Green")))))

This seems to be working as expected.

Rationale:

IF any task is red the milestone becomes yellow (this is to highlight that something is wrong with at least 1 of the 'Children')

IF any milestone is red then the phase becomes yellow (same principle as above)

Issue:

Because my formula is based on dates, if any task has a future date but is completed and I have a late task that is red, the milestone will be yellow as one of the children is red. However, I believe the logic should be based on 'Open" tasks not on all Children as this can mislead the information and health check purpose, BUT I haven't managed to think on something else.

Added a screenshot to ilustrate.

Thanks!

 

Jeff Fike

https://community.smartsheet.com/discussion/comment/230731#Comment_230731

This function has a dangerous admiral ackbar level trap.

the count(chikdren()) function as referenced above does indeed detect a parent row but only if the children of that column is populated. The count function will not count blanks.

so you will need to ensure the parens references a column that is non blank like a primary key for the row.

if(count(children()), ‘>’ 0 doesn’t work if the children are blank.

instead use if(count(children([column name]@row)) ‘>’ 0

for some reason I can’t put the greater than symbol in without the quotes. Don’t use quotes for the greater than symbol in your function.

Paul Newcome

@Jeff Fike You are correct that in certain circumstances using just COUNT(CHILDREN()) could backfire, but in the case of Thaigo's solution, there should not be any blank children. I didn't want to add anything that wasn't needed.

In the case that there is the possibility of having blank children there are two different options.

The first would be using your suggestion of referencing a different column, but you would need to ensure that whichever column you are referencing does not have any blanks in it which brings us back to your original concern.

To avoid it altogether you can count both blank and non-blanks like so:

COUNTIFS(CHILDREN(), OR(@cell = "", @cell <> "")) > 0