Help with a complex Mutli Select Drop down formula!

GScottMiller

Hello!

I have a sheet which is connected to a form where the user can enter what services will be required (as a multi select dropdown option). What I want to be able to do is reference another sheet that has a 'practice' (line of business) that the service is related to and return all those values inside a second drop down

Where I seem to be stuck is at the reference to the Service Type Column in sheet 1. I don't know how to pass on an array of multiple values for it to look up.

Here is my current formula =JOIN(COLLECT({Service / Practice Types | Practice}, {Service / Practice Types | Service}, HAS(@cell, [Service Type]@row)), CHAR(10))

I know I can get the basic concept with the Join + Char(10) formulas.

Reference list (sheet 2 - Service / Practice Types)

Working with only one selection (Sheet 1 - Sheet with Form)

Output when Multiples are selected (Sheet 1 - Sheet with Form)

This is what my desired output would return

It seems like I am real close! I just don't know how to pass on an array(or list, whatever the right term is) to the collect function.

Leibel S

@GScottMiller

Just change around the HAS function in your formula to:

HAS([Service Type]@row, @cell)

Leibel S

@GScottMiller

Just change around the HAS function in your formula to:

HAS([Service Type]@row, @cell)

GScottMiller

@Leibel S

YES! Thank you! I feel stupid!

BraylenStanton

To implement your task, you need to use the FILTER or QUERY function to filter the values of the second dropdown list based on the selected services in the first. For example, if the first dropdown list is in column A, and the associated column with practices is in another sheet, you can use a formula like: '=FILTER(Sheet2!B:B, Sheet2!A:A = A1)

Andrée Starå

Hi @BraylenStanton

I hope you're well and safe!

Unfortunately, Smartsheet doesn't have a FILTER and QUERY function. That's for Excel.

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!