Drop Down Multi Select Formula

Amanda Jeske

Good afternoon everyone. I am trying to figure out the best way to build an "IF" type formula from a multi select column. The formula I tried was: =IF([1. Line of Coverage]@row = "Voluntary Life/ADD", 1, 0). This would put a star in a column to illustrate that a document needs to be collected. This works as long as only "Voluntary Life/ADD" is selected from the Multi Select option, but if more options are selected it doesn't work. Is there a way to do more of something that would look like this: =IF([1. Line of Coverage]@row INCLUDES "Voluntary Life/ADD", 1, 0)?

I hope this makes sense

Genevieve P.

Hi @Amanda Jeske

This makes a lot of sense, and there's a function that does exactly this! It's called the HAS function. This is specifically to see if a multi-select cell has a specific value.

Try this:

=IF(HAS([1. Line of Coverage]@row, "Voluntary Life/ADD"), 1, 0)

Let me know if this works!

Cheers,

Genevieve

Jayy S.

@Genevieve P.

Sorry this is a few years late, but how can I use that same HAS function (or I'm thinking CONTAIN might work better for my case) but include more than one selection from the multi-select column?

For example, I have a multi-select column with options of "Transfer Materials", "Collaboration with MDs", and "Desire for publication", and if someone selected "Transfer Materials" and "Collab with MDs" then I want the cell to return with "A Contract", if "Transfer Materials" and "Desire for publication" are selected then "B Contract", if all are selected then "C Contract", etc. if that makes sense?

Genevieve P.

Hi @Jayy S.

The easiest thing to do here would be to use a Change Cell workflow! You could add your criteria as conditional blocks to indicate when a cell should change value, based on what's selected in another cell: https://help.smartsheet.com/articles/2482299-change-cell-value-in-an-automated-workflow

If you want to stick with a formula, then you'll use an IF(AND scenario:

=IF(AND(HAS([Multi Select]@row, "Transfer Materials"), HAS([Multi Select]@row, "Collaboration with MDs")), "A Contract")

Cheers,
Genevieve

Jayy S.

https://community.smartsheet.com/discussion/comment/441333#Comment_441333

I…forgot Automation exists. Oh wow. Thank you!