Formula to show certain selections of a drop down selection.
I have a sheet with a drop down list (Countries ) and the user will select countries across the world. And I want SS to return only the selections that met criteria. In the image, the user would select items in the country column and the formula would return items from the Europe Countries Column. I feel like this should be easy but I can't figure it out.
Answers
-
There's not a way to run a CONTAINS function against each individual selection in the Country column. Instead you will need to setup a reference sheet that lists your Country values and has the region next to them, and then look that list back up into your first sheet.
So, on a second sheet make two columns:
- Country Lookup: text column where you list each country like "France - Europe" on a row
- Region: text column where you define the region like "Europe" next to each country
Then on the first sheet, in the Europe Countries column, enter this formula:
=JOIN(COLLECT({Country Lookup}, {Country Lookup}, HAS(Country@row, @cell), {Region}, "Europe"), CHAR(10))
For the {Country Lookup} and {Region} parts of the formula, click the Reference Another Sheet link in the formula popup box as you're typing this formula in. Select the second sheet that you created and then select the appropriate column header to select the column. This creates a reference to that column that's used in the formula.
Also you'll notice that you get the results back in plain text and not in "multiple-selection" format. You cannot "build" a multiple-selection result with a formula, that takes Bridge trickery.
Also be sure to "word wrap" the Europe Countries column to see the results in a readable way. The CHAR(10) character is a line feed character that will place each result on a separate line in the cell.
-
Thanks - this solves my problem!
And yes, you can get the results back in a multiple-selection format - just change the column type to drop down list allowing for a multiple selection.
-
Oh nice! I forgot that using CHAR(10) would actually create multiple selection results. Nice catch.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!