Can I create new column restricting values of a "Dropdown (Multi Select)" column to a subset?

Steve Rust

I have a sheet with 4 columns:

Column2 is a "Dropdown (Multi Select)" restricted to 4 values. I created Column3 by hand, restricting the values from Column2 to the subset {Person 1, Person 3}. Is there a formula I can put in Column4 that will make it reproduce Column 3?

Mark Cronk

I thought it was too easy.

Try:

IF(AND(HAS([column 2]@row, "Person 1"), HAS([Column 2]@row, "Person 3"), "Person 1" + char(10)+ "Person 3", IF(HAS([column 2]@row, "Person 1"), "Person 1",IF(HAS([column 2]@row, "Person 3"), "Person 3", "")))

Mark

Mark Cronk

Hi Steve,

If you set column 4 as a multi-select drop down, you can use =column3@row.

What am I missing?

Mark

Steve Rust

Sorry for the confusion, Mark. I created Column3 manually so that you would know what I wanted Column4 to contain. I need a formula to create Column4 from Column2.

Mark Cronk

I thought it was too easy.

Try:

IF(AND(HAS([column 2]@row, "Person 1"), HAS([Column 2]@row, "Person 3"), "Person 1" + char(10)+ "Person 3", IF(HAS([column 2]@row, "Person 1"), "Person 1",IF(HAS([column 2]@row, "Person 3"), "Person 3", "")))

Mark

Steve Rust

Mark:

After adding a parenthesis and fixing the column references, this worked. Here is the final formula:

=IF(AND(HAS([Column2]@row, "Person 1"), HAS([Column2]@row, "Person 3")), "Person 1" + CHAR(10) + "Person 3", IF(HAS([Column2]@row, "Person 1"), "Person 1", IF(HAS([Column2]@row, "Person 3"), "Person 3", "")))

I thought about nested IF-THENs but would never have gotten the syntax right on my own. Thank you so much.

The real smartsheet has 7 people in it which results in 128 theoretically possible values which would require a lot of nesting. Practically, I could probably restrict myself to no more than pairs, but even that allows 29 different values. I may write a script that will produce the formula for me, though now I'm wondering if there is a limit of the number of times you can nest IF-THENs.

Thanks again for your help.

- Steve

Mark Cronk

Good luck Steve. Thank you for contributing to the Community.

Mark