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

  • PeggyLang
    PeggyLang ✭✭✭✭✭✭

    @Genevieve P.

    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,
    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • PeggyLang
    PeggyLang ✭✭✭✭✭✭

    @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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!