Identifying specific combinations from a multi-select field
Good morning, fellow Smartsheeters.
I have a master roadmap sheet that we use to populate numerous dashboards and reports.
One of the columns on this roadmap is Project Type.
It is a multi select drop down with the following values:
Revenue Opportunity, Revenue Loss Avoidance, Risk Compliance & Cost Savings
The stakeholders stated that any projects that are soley Revenue Loss Avoidance or Risk Compliance should not appear on the dashboards or reports.
Simple enough, I identify them using the following formula to check a box. If the box is checked, the row is excluded from the dashboards/reports.
=IF(OR([Project Type]1 = "Risk Compliance", [Project Type]1 = "Revenue Loss Avoidance"), 1, 0)
I just noticed that some projects contain BOTH Risk Compliance and Revenue Loss Avoidance - which my above formula is failing to capture.
I'd like to add another condition to the formula to catch this instance, but haven't been able to identify multiple selections correctly.
I can't use 'contains' because if a project has, say, Risk Compliance and Revenue Opportunity it needs to appear on the dashboard.
Scratching my head here, so I appreciate any help.
Thanks a ton,
-Alan
Answers
-
Hi @Alan Rappa
The issue here is that you have a multi-select column, and your formula is only looking for one result. It will only look for if the cell has a single selection that equals exactly what you have "in these". You'll want to add in a HAS function, which will check to see if the cell HAS either of these criteria! (Even if both are selected, or one is selected with another option).
HAS was created specifically for multi-select columns. Try this:
=IF(OR(HAS([Project Type]@row, "Risk Compliance"), HAS([Project Type]@row, "Revenue Loss Avoidance")), 1, 0)
Cheers!
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
thanks so much @Genevieve P
I'll give this a shot, though would this work for all my criteria?
I need to be able to identify when Project type is Risk Compliance, Revnue Loss Avoidance, and Risk Compliance + Revenue Loss Avoidance only.
To clarify a little more:
If Revenue Loss Avoidance = Check
If Risk Compliance = Check
If Revenue Loss Avoidance + Risk Compliance = Check
If Revenue loss Avoidance + Cost Savings = Uncheck
If Risk Compliance + Revenue Opportunity = Uncheck
If I use HAS, would those last two scenaros be correct? or would they be checked because Project Type has Risk Compliance or Revenue Loss Avoidance?
Thanks so much
-
Hi @Alan Rappa,
My apologies, I misunderstood the potential options. Your last two scenarios would also be checked because they contain one of the possible options. To fix this, instead of using HAS for each individual criteria then, you can keep your original formula as-is (looking for if one of those values appears on its own), and then add a third criteria using AND and HAS together, looking for when those two options appear together.
Try this:
=IF(OR([Project Type]@row = "Risk Compliance", [Project Type]@row = "Revenue Loss Avoidance", AND(HAS([Project Type]@row, "Risk Compliance"), HAS([Project Type]@row, "Revenue Loss Avoidance"))), 1, 0)
Now, keep in mind that if these two selections were both in the cell with a third option, this would check:
If Revenue Loss Avoidance = Check
If Risk Compliance = Check
If Revenue Loss Avoidance + Risk Compliance = Check
If Revenue loss Avoidance + Cost Savings = Uncheck
If Risk Compliance + Revenue Opportunity = Uncheck
If Risk Compliance + Revenue Opportunity + Revenue Loss Avoidance = Check
Test this out and let me know if it works for you or if you have any other questions!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thanks @Genevieve P
Certainly getting closer (and I need to get in the habit of using @row - it just seems fussy for me).
Now if there was a way I could exclude that last scenario you mentioned I'd be in business :)
Thanks so much!
-
I wonder if I can do this backwards...
meaning, if Project type has Revenue Opportunity or Cost savings then don't check, else Check
What do you think?
edit: This actually works, EXCEPT for one unforseen scenario
The box is checked when PROJECT TYPE is blank.... ugghhhhhh
Trying to figure out how to get that out of the equation as well :(
-
Hmm, that's actually a really good point. We could do the opposite! You only have the 4 options, is that correct? (Revenue Opportunity, Revenue Loss Avoidance, Risk Compliance, Cost Savings)
If so, we can just say that if that cell has either Risk Compliance OR Cost Savings, don't check the box. Otherwise, check! This will then apply to all of your scenarios above... whether there's one or two of the other options selected.
Try this:
=IF(OR(HAS([Project Type]@row, "Revenue Opportunity"), HAS([Project Type]@row, "Cost Savings")), 0, 1)
Great idea!
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Slight edit to not check boxes where the [Project Type] is blank...
=IF(OR([Project Type]@row = "", HAS([Project Type]@row, "Revenue Opportunity"), HAS([Project Type]@row, "Cost Savings")), 0, 1)
-
@Paul Newcome Brilliant, thank you!!
@Alan Rappa, use Paul's one instead ^^
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
No worries. I just happened to notice the comment about there being a problem with blank rows being checked. I see that it is an edit, so it may have been posted after your solution.
-
thank you @Genevieve P & @Paul Newcome !
I was gunning for this:
=IF(OR([Project Type]@row = "", HAS([Project Type]@row, "Revenue Opportunity"), HAS([Project Type]@row, "Cost Savings")), 0, 1)
But I was messing up the approach.
I ended up with
=IF(OR(HAS([Project Type]@row, "Cost Saving"), HAS([Project Type]@row, "Revenue Opportunity"), ISBLANK([Project Type]@row)), 0, 1)
Which works as far as I can tell.
wondering why it didn't like it when I used [Project Type]@row = "" originally?
Thanks again for the help with this. It was throwing my dashboard numbers completely off :)
I owe you both!
-
Happy to help. 👍️
-
Glad you figured it out in the end!! 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!