Collect values in multi-select field from different lookup sheet

Hi,

I have two sheets. The first sheet (destination sheet) has the multi-select column 'Value', and the second the values I want to collect based on Project ID (unique identifier) between sheets. The unique identifier could appear more than once, so incorporated the Row ID in lookup sheet if required.

I don't think the helper row is required but just added it.

The result I am looking for is depicted in the 1st screenshot below.

Lookup/source sheet:



Some assistance will be appreciated.

Regards,

Pierre

Best Answer

Answers

  • Pierre Mostert
    Pierre Mostert ✭✭✭✭

    Hi Chris

    Thanks it works great. Much appreciated.

    I just have one more question.

    How do I adapt the formula to only collect the values IF the flag is TRUE in the source sheet? See modified screenshot below:

    Regards,

    Pierre

  • Pierre Mostert
    Pierre Mostert ✭✭✭✭

    Hi Chris

    No worries, I figured it out. I see the COLLECT already acts as an IF statement. I just had to add the second condition to include TRUE.

    Thanks again!

    Pierre

  • Hello. I am not sure if any of you will get a notification when I add this comment but just going to give it a shot. I am actually trying to achieve the exact opposite of this. The source sheet actually has a multi select dropdown and I want to seperate it into multiple rows into the destination sheet. Any ideas? Thanks in advance.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @NikhilB

    There currently isn't a way to parse out the data from a multi-select cell into individual, separate values each in their own row. Please let the Product team know that this is a feature you would find useful by filling in this form, here.

    Thanks!

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!