Auto-Populating Field Based On Multiple Selections

Options

Hello,

I currently have a Smartsheet form that allows users to select multiple options:

Each option corresponds to a specific contact name:

I need to come up with a formula that auto-populates every contact name in one row based on the choices the user selects on the form. I figured out a way to do so when they only select one option, but we need to have users select multiple options (there are over fifty options to choose from).

Tags:
«1

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    There is no way to do this with a formula and have the output list multiple usable contacts. It would just be a text string that lists out everyone associated.


    If that is ok, you would use a JOIN/COLLECT combo.

    =JOIN(COLLECT({Reference Table Contact Column}, {Reference Table Option Column}, HAS([Multi-Select Column]@row, @cell)), "; ")

  • Benjamin O'Leary
    Options

    @Paul Newcome I copy-pasted your formula, and changed the references to match my contacts/options, and I'm getting an #UNPARSEABLE error.

    =JOIN(COLLECT({EDMS Contact Column}, {EDMS PCQT Name}, HAS([EDMS All]@row, @cell)), "; ")

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Are you able to provide a screenshot of the formula open in the sheet similar to the screenshot below?


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Do you have a column called "EDMS All" in your sheet? If so, try clicking on the cell instead of typing it in. It looks like it is not registering that column name for some reason and could be a hidden space somewhere.

  • Benjamin O'Leary
    Options

    @Paul Newcome I don't think I fully understand what 'Multi-Select Column' means. I thought that meant to highlight both the Reference Table Option column and the Reference Table Contact column.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Whatever column is housing the data where users select multiple options.

  • Benjamin O'Leary
    edited 06/16/23
    Options

    @Paul Newcome What if there are multiple columns housing data where users select multiple options? There are approximately 21 columns, each containing parts of data that is present in the EDMS PCQT Name column.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Then you would need to set up separate JOIN/COLLECT formulas for each multi-select dropdown column and "add" them together.

    =JOIN(COLLECT(...........)) + "; " + JOIN(COLLECT(................))

  • Benjamin O'Leary
    Options

    I tried that and still got an #UNPARSEABLE error

    =JOIN(COLLECT({PCQT Lead Contact Reference List Range 1}, {PCQT Lead Contact Reference List Range 2}, HAS([Automation (Corelab)]@row, @cell)) + "; " + 

    JOIN(COLLECT({PCQT Lead Contact Reference List Range 1}, {PCQT Lead Contact Reference List Range 2}, HAS([Cardiometabolic (Corelab)]@row, @cell)) + "; " + 

    JOIN(COLLECT({PCQT Lead Contact Reference List Range 1}, {PCQT Lead Contact Reference List Range 2}, HAS([Coagulation, Hematology, Urinanalysis (Corelab)]@row, @cell)) + "; " +

    JOIN(COLLECT({PCQT Lead Contact Reference List Range 1}, {PCQT Lead Contact Reference List Range 2}, HAS([CoreLab Reagents (Corelab)]@row, @cell)) + "; " +

    JOIN(COLLECT({PCQT Lead Contact Reference List Range 1}, {PCQT Lead Contact Reference List Range 2}, HAS([Custom Biotech (Corelab)]@row, @cell)) + "; " +

    JOIN(COLLECT({PCQT Lead Contact Reference List Range 1}, {PCQT Lead Contact Reference List Range 2}, HAS([Infectious Disease/RBSS (Corelab)]@row, @cell)) + "; " +

    JOIN(COLLECT({PCQT Lead Contact Reference List Range 1}, {PCQT Lead Contact Reference List Range 2}, HAS([Oncology (Corelab)]@row, @cell)) + "; " +

    JOIN(COLLECT({PCQT Lead Contact Reference List Range 1}, {PCQT Lead Contact Reference List Range 2}, HAS([SWA Systems (Corelab)]@row, @cell)) + "; " +

    JOIN(COLLECT({PCQT Lead Contact Reference List Range 1}, {PCQT Lead Contact Reference List Range 2}, HAS([Blood Screening (Molecular Lab)]@row, @cell)) + "; " +

    JOIN(COLLECT({PCQT Lead Contact Reference List Range 1}, {PCQT Lead Contact Reference List Range 2}, HAS([Infectious Diseases (Molecular Lab)]@row, @cell)) + "; " +

    JOIN(COLLECT({PCQT Lead Contact Reference List Range 1}, {PCQT Lead Contact Reference List Range 2}, HAS([Oncology & Genetics (Molecular Lab)]@row, @cell)) + "; " +

    JOIN(COLLECT({PCQT Lead Contact Reference List Range 1}, {PCQT Lead Contact Reference List Range 2}, HAS([PCR Systems Workflow & IT (Molecular Lab)]@row, @cell)) + "; " +

    JOIN(COLLECT({PCQT Lead Contact Reference List Range 1}, {PCQT Lead Contact Reference List Range 2}, HAS([Seq Systems (incl. Inforatics) (Molecular Lab)]@row, @cell)) + "; " +

    JOIN(COLLECT({PCQT Lead Contact Reference List Range 1}, {PCQT Lead Contact Reference List Range 2}, HAS([Digital Pathology (Pathology Lab)]@row, @cell)) + "; " +

    JOIN(COLLECT({PCQT Lead Contact Reference List Range 1}, {PCQT Lead Contact Reference List Range 2}, HAS([Oncology Assays (Pathology Lab)]@row, @cell)) + "; " +

    JOIN(COLLECT({PCQT Lead Contact Reference List Range 1}, {PCQT Lead Contact Reference List Range 2}, HAS([Personalized Health Care Solutions (Pathology Lab)]@row, @cell)) + "; " +

    JOIN(COLLECT({PCQT Lead Contact Reference List Range 1}, {PCQT Lead Contact Reference List Range 2}, HAS([Systems Workflow & IT (Pathology Lab)]@row, @cell)) + "; " +

    JOIN(COLLECT({PCQT Lead Contact Reference List Range 1}, {PCQT Lead Contact Reference List Range 2}, HAS([Blood Gas/ Hospital Blood Glucose (Point of Care)]@row, @cell)) + "; " +

    JOIN(COLLECT({PCQT Lead Contact Reference List Range 1}, {PCQT Lead Contact Reference List Range 2}, HAS([Clinical Chem/ Immunology(Point of Care)]@row, @cell)) + "; " +

    JOIN(COLLECT({PCQT Lead Contact Reference List Range 1}, {PCQT Lead Contact Reference List Range 2}, HAS([LIAT(Point of Care)]@row, @cell)), "; ")

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Each of your individual JOIN/COLLECTs have missing pieces with the exception of the very last one.


    =JOIN(COLLECT(.................), "; ") + "; " + JOIN(COLLECT(.................), "; ") + "; " + .......................

  • Benjamin O'Leary
    Options

    Thank you @Paul Newcome the formula seems to work. The only issue I'm seeing now is that the cells where the formula is active has multiple ; symbols:

    Any idea why this is occurring?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    It is the + "; " + in between each of the JOIN/COLLECT pieces. You may want to incorporate an IF statement to say if the cell is not blank then output the "; ", otherwise leave blank.


    =JOIN(COLLECT([first column]), "; ") + IF([First Column]@row <> "", "; ", "") + JOIN(COLLECT([second column]), "; ") + IF(.............

  • Benjamin O'Leary
    Options

    I don't want the ; symbol to appear when a cell is not blank; I need to the email addresses to be present.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!