Join(distinct(collect))) Multi-select column

I'm wanting to create a list of Markets for a selection of sites in a multi-select column.

Based on other community entries, I'm using:

=JOIN(DISTINCT(COLLECT({Site Market Range}, {Site list Range}, CONTAINS(Site@row, @cell)), ", "))

This works when there is 1 selection in the multi-select field but as soon as I add more than one site it goes blank as if it doesn't match anything.

I'm guessing the Site@row is look at the cell as 1 block of text and since it doesn't match anything in my list of single entries I am out of luck. Is this true?


I have 70-ish sites some creating all combinations of them would be near impossible.


Thanks.

Robert Meisch

Mgr Dep Ops & Smartsheet Success Team

Sysco

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Robert Meisch

    You are correct: the formula sees the cell with multi-selections as one full string and is looking for the full match in your reference table. There currently isn't a way for a formula in Smartsheet to read a multi-select cell and parse it out, searching for individual items in a single table. Your reference table would need to have all possible combinations of selections and the output you want for it to match up the value in your "Site" column with the value in your reference sheet.

    Please submit your feedback to the Product team through this form, here!

    In this instance, it looks like you only have four values to output: North, South, East, and West, is that correct?

    If so, what about setting up 4 separate Change Cell Workflows? Each workflow would look for all the selections that could be selected for a Market. Then the output would be that it updates a multi-select cell with that value (without replacing the other values that may be populated in that cell):


    See: Change the Value of a Cell in an Automated Workflow

    Cheers,

    Genevieve

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!