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?
Best Answer

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

Hi Steve,
If you set column 4 as a multiselect 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 IFTHENs 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 IFTHENs.
Thanks again for your help.
 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
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!