Harvey Balls Using Formulas & Multiple Columns
Hi all!
I am trying to determine what formula to use when calculating Harvey Balls using two different column details. Harvey Balls are in the "Corrective Action" column.
What I want to do:
Harvey Ball to be Full when all children rows are checked in "done" column AND there is text in the "Response" column
Harvey ball to be 3/4 full when text is in the "Response" column and "Done" is not checked
Harvey Ball to be 1/2 full when one "Done" is checked and "Response" has text
Harvey Ball to be empty when nothing is checked.
Thoughts if this is possible? I've taken some tries at this, but I keep getting error messages. Screenshot below!
Best Answer
-
Ok. Give something like this a try...
=IF(COUNTIFS(CHILDREN(Done@row), @cell = 1, CHILDREN(Response@row), @cell <> "") = COUNTIFS(CHILDREN(Done@row), OR(@cell = "", @cell <> "")), "Full", IF(COUNTIFS(CHILDREN(Done@row), @cell = 1, CHILDREN(Response@row), @cell <> "")>= 1, "Half", "Empty"))
If they are all checked and all have responses = "Full"
If at least one is checked and has a response = "Half"
All else = "Empty"
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
A couple of questions...
1) The 1/2 full: Is that when the Done and Response are filled in on the parent row, or if at least one child row has that?
2) 3/4 full: Would that override the above?
3) Exactly which row(s) are you planning on putting this formula in?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
- The latter. If one is checked w/ response text to make it 1/2 full.
- I wouldn't mind nixing this 3/4 full logic as it may override (it's not super important to have logic for all HArvey Ball options
- I'm planning on putting this in only the parent rows (there are 14 parent rows)
-
Ok. So far we have...
Harvey Ball to be Full when all children rows are checked in "done" column AND there is text in the "Response" column in all child rows
Harvey Ball to be 1/2 full when one "Done" is checked and "Response" has text on at least one child row.
Harvey Ball to be empty when nothing is checked in the child rows.
Additional variables:
What if all children are checked but none have a response?
What if all have a response but none are checked? --> This one would currently be set to "Empty" since nothing is checked.
Clarification on location of formula: In your screenshot, you have two layers of parent rows. Would it be in the topmost parent (black highlight) or the immediate parent ("test text" in Primary Column)?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
it would be placed in the immediate parents, one without color!
If nothing is in the text and the boxes are checked, empty would be fine, as it would alert something needs to be added!
Thanks for your help :)
-
Ok. Give something like this a try...
=IF(COUNTIFS(CHILDREN(Done@row), @cell = 1, CHILDREN(Response@row), @cell <> "") = COUNTIFS(CHILDREN(Done@row), OR(@cell = "", @cell <> "")), "Full", IF(COUNTIFS(CHILDREN(Done@row), @cell = 1, CHILDREN(Response@row), @cell <> "")>= 1, "Half", "Empty"))
If they are all checked and all have responses = "Full"
If at least one is checked and has a response = "Half"
All else = "Empty"
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
That worked! Whoop! Thanks a ton!!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!