Formula to Activate the Empty, Quarter, Half, Three Quarter, & Full harvey balls in a STATUS column

Options
ALMF
ALMF ✭✭
edited 01/02/20 in Formulas and Functions
This discussion was created from comments split from: Formula to Change Harvey Ball Color Based off of a Word.

I'm struggling with this formula. I have a drop-down column called STAGE, where there are 17 stages listed. I'd like these options to activate the Empty, Quarter, Half, Three Quarter, and Full harvey balls in a STATUS column. For example, 5 of the options in the STAGE column are NEED INFO, BRIEF RECEIVED, PRODUCTION, APPROVAL, DELIVERED. Where do I put the brackets so that these words populate harvey balls in the STATUS column (Empty, Quarter, Half, Three Quarter, Full, respectively)?

Best Answer

«1

Answers

  • ALMF
    ALMF ✭✭
    Options

    I'm struggling with this formula. I have a drop-down column called STAGE, where there are 17 stages listed. I'd like these options to activate the Empty, Quarter, Half, Three Quarter, and Full harvey balls in a STATUS column. For example, 5 of the options in the STAGE column are NEED INFO, BRIEF RECEIVED, PRODUCTION, APPROVAL, DELIVERED. Where do I put the brackets so that these words populate harvey balls in the STATUS column (Empty, Quarter, Half, Three Quarter, Full, respectively)?

  • Gwyneth C
    Gwyneth C ✭✭✭✭✭✭
    Options

    Split this question out from the original post ... in the original thread @Mike Wilday asked:

    Can you list out each of the 17 stages and what they should populate?

  • ALMF
    ALMF ✭✭
    Options

    Sure! Thanks @Mike Wilday ! And sorry @Gwyneth C, I answered in the original post while you were responding. Hope this is in the right place now.

    --

    NEED INFO, Empty

    ASSIGN, Empty

    BRIEF RECEIVED, Quarter

    PRE-PRODUCTION, Quarter

    PRODUCTION, Half

    APPROVAL, Three Quarter

    REVISIONS, Three Quarter

    REVIEW, Three Quarter

    SFX/GFX, Half

    DELIVERED, Full

    KILLED, Empty

    LEGAL, Three Quarter

    N/A, Empty

    RFD, Three Quarter

    HOLD, Empty

    TBD, Empty

    QC, Three Quarter

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    Try this formula...

    =IF(OR(Status@row = "Need Info, Status@row="ASSIGN", Status@row="Killed", Status@row = "N/A", Status@row = "HOLD", Status@Row="TBD"), "Empty", IF(OR(Status@row = "Brief Received", Status@row="Pre-Production"), "Quarter", IF(OR(Status@row="PRODUCTION", Status@row="SFX/GFX" ), "Half", IF(OR(Status@row="APPROVAL", Status@row="REVIEW", Status@row = "LEGAL", Status@row="RFD", Status@row="QC"), "Three Quarter", IF(Status@row="Delivered", "Full")))))

    I would also consider changing the method to a vlookup formula that looks up the status in a new sheet and returns the correct phase. But this will work if you don't want to rewrite it a vlookup.

  • ALMF
    ALMF ✭✭
    Options

    That looks like it should be correct, but I'm still getting an #unparseable error. I think it has to do with brackets and spaces in some of my status names. Thanks for the help! I'll keep tinkering, and will look into a vlookup option -- this may be more complicated than I realized.

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    Is your status column name called Status? OR does it have a different name? A screenshot of your sheet with the sensitive data hidden would really help me to determine what your needs are and what might be going on.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    @Mike Wilday

    Thanks for stepping in. I think @Gwyneth C got us confused. 😋


    @almrie

    If you post the exact formula you are using along with your exact column name(s), we may be able to take a look and do some troubleshooting for you. Screenshots as mentioned above by Mike W. would also be very useful.


    I also second the idea of building a table. It would allow for much more flexibility if you need to add, remove, or change any of those 17 different statuses.

  • ALMF
    ALMF ✭✭
    Options

    The STATUS column is where I'd like to see harvey balls. The STAGE column is where we have our 17 different stages, which refer to various stages of completion. When I pasted your formula into the STATUS column, I did change every Status mention to STAGE, since this is the column that has the values. Here is a screenshot of a sandbox where I am experimenting with formulas.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 01/02/20
    Options

    It should be working if you updated your column name in the formula. Can you copy/paste the exact formula? It may be an errant comma or something.


    EDIT: I just took another look at Mike's formula. If you copy/pasted that from here, then you will need to add in a missing quotation mark between Need Info and the comma right after.


    =IF(OR(Status@row = "Need Info"

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    Yep, Paul's right. I was missing that quotation mark.

    here's the revised version. @Paul Newcome Thanks!

    =IF(OR(Status@row = "Need Info", Status@row="ASSIGN", Status@row="Killed", Status@row = "N/A", Status@row = "HOLD", Status@Row="TBD"), "Empty", IF(OR(Status@row = "Brief Received", Status@row="Pre-Production"), "Quarter", IF(OR(Status@row="PRODUCTION", Status@row="SFX/GFX" ), "Half", IF(OR(Status@row="APPROVAL", Status@row="REVIEW", Status@row = "LEGAL", Status@row="RFD", Status@row="QC"), "Three Quarter", IF(Status@row="Delivered", "Full")))))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    @Mike Wilday No worries at all. Especially considering it kind of got shoved on you by Gwyneth. Hahaha

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    Based on your columns in your sheet... this is the revised formula. Does this work?

    =IF(OR(stage@row = "Need Info", stage@row="ASSIGN", stage@row="Killed", stage@row = "N/A", stage@row = "HOLD", stage@Row="TBD"), "Empty", IF(OR(stage@row = "Brief Received", stage@row="Pre-Production"), "Quarter", IF(OR(stage@row="PRODUCTION", stage@row="SFX/GFX" ), "Half", IF(OR(stage@row="APPROVAL", stage@row="REVIEW", stage@row = "LEGAL", stage@row="RFD", stage@row="QC"), "Three Quarter", IF(stage@row="Delivered", "Full")))))

  • ALMF
    ALMF ✭✭
    Options

    Here is the formula with the updated column name. I'm still getting the unparseable error. Some of our stages have spaces and slashes in the stage name. Perhaps that confuses things?

    =IF(OR(STAGE@row = "Need Info, STAGE@row="ASSIGN", STAGE@row="Killed", STAGE@row = "N/A", STAGE@row = "HOLD", STAGE@Row="TBD"), "Empty", IF(OR(STAGE@row = "Brief Received", STAGE@row="Pre-Production"), "Quarter", IF(OR(STAGE@row="PRODUCTION", STAGE@row="SFX/GFX" ), "Half", IF(OR(STAGE@row="APPROVAL", STAGE@row="REVIEW", STAGE@row = "LEGAL", STAGE@row="RFD", STAGE@row="QC"), "Three Quarter", IF(STAGE@row="Delivered", "Full")))))


    I am reading about tables. I like @Paul Newcomethe idea of flexibility, so I'm looking into other ways of doing this.

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    @almrie The issue is with the missing quote in the first OR "Need Info doesn't have an end-quote. Try the updated formula I just put in this thread.

  • ALMF
    ALMF ✭✭
    Options

    Thanks for the help @Mike Wilday ! The revised formula is still unparseable.

    Are formulas case-sensitive? Verifying that now. (Our column names and stages are all-caps.)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!