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
 Smartsheet Customer Resources
 62.2K Get Help
 358 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 135 Brandfolder
 127 Just for fun
 128 Community Job Board
 444 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!