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
Comments
-
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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 459 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 137 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives