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

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

Find more posts tagged with

Accepted answers

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

All comments

Paul Newcome

@almrie

To build the table, it would look something like this (column headers in bold)...

[Lookup Stage] Value

Need Info Empty

ASSIGN Empty

Killed Empty

N/A Empty

HOLD Empty

TBD Empty

Brief Received Quarter

Pre-Production Quarter

so on and so forth.......

Then your formula would look something like this:

=VLOOKUP(Stage@row, [Lookup Stage]:Value, 2, false)

What this does is takes the status in the Stage column, finds it in the [Lookup Stage] column, and pulls the data from the Value column from the appropriate row.

This gives you the ability to add/delete/change values as needed without having to get into the actual formula itself.

Paul Newcome

@almrie

I took a look at the formula you provided. In addition to the missing quote, one of the @row references has a capital R. That will throw it off every time.

STAGE@Row = "TBD"

should be

STAGE@row = "TBD"

In answer to your question: Column names are not case sensitive.

ALMF

Thanks, @Paul Newcome . This explanation is clear. I'm reading about tables now and I will update this thread when I'm stuck or able to create the desired results.

@Mike Wilday, here is a revised formula with cases matched and all quotes accounted for. Still unparseable. I'll keep tinkering:

=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")))))

Mike Wilday

@Paul Newcome Smartsheets is killing me. :|

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

ALMF

@Paul Newcome Great! I changed the errant "R" to "r" and Mike's formula works now! I'll continue to read about VLOOKUP and will attempt this solution for greater flexibility.

@Mike Wilday, thanks for jumping in! Your formula helped me better understand IF/OR logic.

I took a few formulas sessions at ENGAGE19, but I'm only just now applying them to my own sheets. It's fun and you both are very helpful!

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

Mike Wilday

Absolutely! Glad I could be of help.

Sorry for the confusion and the typos! Definitely gotta pay more attention to those @row statements! Thank you.

Paul Newcome

@Mike Wilday No worries. We all have those days. I didn't even realize that the @row IS actually case sensitive until I was helping someone else troubleshoot a formula. I had noticed the capital R's, but didn't think anything of it. Right before I suggest they reach out to support, I tried it out as a shot in the dark. Low and behold! There was the answer.

@almrie

Happy to help! 👍️

Please go ahead and mark Mike's most recent formula (the one with the quotes and lower case r 😋) as the "Accepted Answer". That way others know that a solution has been found.

Also... Feel free to re-visit or create another post (you can @mention me in it to get my attention) if you would like additional help on setting up the table. It can be built either in the same sheet or in a different sheet if you would like. It is definitely very flexible, but it does still have it's limitations/restrictions. (A different post is recommended since it will be a very different solution).

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.