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.

  • 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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!