Create RYG Balls for Children

cramsey

I've spent hours attempting to display RYB Balls for three different drop-down status. 

"Not Started" 

"Pending"

"Signed" 

The goal is to have a "Red ball" display in the Not Started Column for the drop down "Not started" in the Quote status children.

The formula I'm attempting to adjust - =COUNT(CHILDREN([Quote Status]2 = "Not Started", "Red")

 

I'm not having any luck. Please see screenshot 

J. Craig Williams

I assume the [Not Started] column is a Symbol column.

I assume you are using COUNT because there are multiple children.

Do you want the RYG to be red if ANY children are "Not Started"?

If so, I would use MATCH() or IF(COUNTIFS()..

Or if a certain number are "Not Started"?

If so, I would use IF(COUNTIFS()...

...

=IF(COUNTIFS(CHILDREN([Quote Status]@row),"Not Started")>0,"Red","")

should get you started either way.

Craig

 

 

 

cramsey

Your amazing!! 

I believe you forgot to add the remainder for yellow, green, and blue for the drop-down quote status. 

Red = Identifies rows containing a quote with a “Not Started” status

Yellow = Identifies rows containing a quote with a “Pending S” status 

Blue = rows containing a quote with a “Pending E” Status 

Green = Identifies rows containing a quote with a “Signed” Status 

 

You are correct, I have the "Not Started" Column as symbols. 

You are correct, there are multiple children

J. Craig Williams

Determining a parent's status based on the children's status is something that is not universal. I'd need to know more than I do, so I tried to give you the tools to do the rest.

It the criteria was

if any "X" then "X"

else if any "Y" then "Y"

else if any "Z" then "Z"

then you could take this 

=IF(COUNTIFS(CHILDREN([Quote Status]@row),"Not Started")>0,"Red","TBD")

and where it says "TBD", replace with a whole if statement, like this:

=IF(COUNTIFS(CHILDREN([Quote Status]@row),"Not Started")>0,"Red", IF(COUNTIFS(CHILDREN([Quote Status]@row),"Pending S")>0,"Yellow","TBD"))

and so on. 

But it usually isn't that simple.

The Nested IF's are done in order left to right, so you need to decide on which one takes precedence and write the formula for that.

Remember this formula is looking at ALL the children.

When you want to just know the color for the children's row, not the parent row, then you won't use COUNTIFS, but just the Nested IF's

=IF([Quote Status]@row = "Not Started","Red", IF([Quote Status]@row = "Pending S","Yellow","TBD"))

I hope that helps.

Craig