Create RYG Balls for Children

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

Find more posts tagged with

Support

IT & Operations

Project Management

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Sheet -Ganhtt Chart
Is it possible to display both the planned and actual duration for a single task on a Smartsheet Gantt chart? I would like the Gantt bar to show the planned duration (e.g., in blue) based on a Planned End Date column, and then show the actual duration (e.g., in green) based on an Actual End Date column. Can the Gantt view…
Color-Coded Tags in Board View
I noticed in the Board View tags are grey by default but in the how to videos, in the Board View some of the tags are colorful. I'd like to leverage that same functionality as I'm building a research repository with many different types of research in it and a visual cue would be lovely. Can anyone assist? I attached a…
Autonumber Not Working
Hi, We I use the edit column properties and select autonumber, the sheet no longer generates a number. Please help.