Formula to show certain selections of a drop down selection.

AnnaKisting

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.

Brian_Richardson

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.

AnnaKisting

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.

Brian_Richardson

Oh nice! I forgot that using CHAR(10) would actually create multiple selection results. Nice catch.