Auto-Populating Field Based On Multiple Selections

Benjamin O'Leary

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).

Paul Newcome

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

@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

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

Benjamin O'Leary

@Paul Newcome here you go:

Paul Newcome

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

@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

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

Benjamin O'Leary

@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

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

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

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

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

Benjamin O'Leary

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?

Benjamin O'Leary

Paul Newcome

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

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