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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Sam Clapp
    Sam Clapp ✭✭✭
    1. The latter. If one is checked w/ response text to make it 1/2 full.
    2. 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
    3. I'm planning on putting this in only the parent rows (there are 14 parent rows)


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Sam Clapp
    Sam Clapp ✭✭✭

    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 :)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Sam Clapp
    Sam Clapp ✭✭✭

    That worked! Whoop! Thanks a ton!!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!