Extract values from a dropdown list with multiple selections and place each value into its own cell
Hello
Hoping someone can help a newbie with trying to figure out how I can go about taking selections from a drop box selection with multiple values and placing them into their own cell, to allow for a VLOOKUP of each value to pull information from another sheet. the VLOOKUP and I can figure out once I get each value into its own cell.
Example:
I have selected 2 store numbers from the drop downlist, 1185 and 1188. I can see these values in the cell on my sheet. I want to extract those values and place them into another sheet, each on their row, to allow for a vlookup to reference another sheet and populate the required information for each store I selected. The formula to extract and place each value into their own cell will need to be dynamic as I may need to place 2 stores, 20 stores or 50 stores.
Can anyone help with this?
Answers
-
Hey @Copetope
There currently isn't a way to extract individual selections from a multi-select cell and parse them into multiple, separate cells.
Please feel free to add your vote and voice to this Product Idea when you have a minute: Add function to retrieve values from cell for a multi-select column
That said, if you already have a reference sheet somewhere with your Store information on individual rows, you could use a JOIN(COLLECT formula to return data from that second sheet into your current sheet with the multi-select.
=JOIN(COLLECT({Column to Return}, {Single Select Column}, HAS(Multi@row, @cell)), ", ")
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
I am looking to do something very similar. I have a list of technicians each of whom look after a set of stores (ie 4 or 5 different stores). I pull together a ‘after hours on call’ sheet whereby I may assign ‘John Smith’ to be on call for his store set, Joe’s store set and Mark’s store set.
my hope was to have a multi select cell where I could enter John, Joe and Mark and the cell beside it would populate with all three of their store sets into one multi select cell. I need the cell with all store #’s to be multi select as I have another sheet that employs a formula containing ‘Has’ so need to make sure I am able to identify any one of the individual stores.
Thoughts? -
Hi @PeggyLang
Did you try the formula structure above? The one tweak you may need to make is to have the separator be CHAR(10) instead of a comma so that it parses out the values into multi-select values.
=JOIN(COLLECT({Column with Stores}, {Single Select Name Column}, HAS(Multi@row, @cell)), CHAR(10))
Cheers,
GenevieveNeed more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Genevieve P.
BINGO!!!! It worked and I can't even quantify the time savings to our technicians this will result in.
THANK YOU!!!!
Happy 4th of July!!! -
Amazing! I'm glad to hear this helped 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 464 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!