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

ALMF

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

Mike Wilday

@Paul Newcome Smartsheets is killing me. :|

Sorry @almrie , that is my fault too. try this formula.

=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

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

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

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

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

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

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

@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

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.

Screen Shot 2020-01-02 at 2.41.36 PM.png

Paul Newcome

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

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

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

Mike Wilday

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

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

@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

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