Help with a complex Mutli Select Drop down formula!



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.


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!