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

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?

• ✭✭✭✭✭✭

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

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• ✭✭✭✭✭✭

Hi Steve,

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

What am I missing?

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

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

• ✭✭✭✭✭✭

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

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

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

- Steve

• ✭✭✭✭✭✭

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

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!