Multi-Select formula using the selections as the criteria.

See attached document for details, but I need to select email addresses from an external sheet where the contents of a multi-select column is used as the criteria.

This was my attempt. No errors, but it doesn't return anything.

=JOIN(COLLECT({Email Address}, {Dealer Name}, HAS(@cell, DEALER@row)), CHAR(10))

Find more posts tagged with

Formulas

Accepted answers

earl_bennett

UPDATE: Using either CONTAINS or HAS works with only one value selected in the drop down, but when selecting more than one it doesn't return anything.

All comments

earl_bennett

UPDATE: Using either CONTAINS or HAS works with only one value selected in the drop down, but when selecting more than one it doesn't return anything.

Bassam Khalil

Hi @earl_bennett

Multi-select columns can be tricky because they store multiple values in a single cell, separated by commas, which complicates the usage of functions like HAS() or CONTAINS() for matching multiple criteria at once.

Here’s how you can approach solving this issue:

When you use HAS(@cell, DEALER@row) or CONTAINS(@cell, DEALER@row), these functions are designed to work with single values. In the case of a multi-select column, if a user selects more than one value, these functions attempt to match the entire string (e.g., "Dealer1, Dealer2") against each entry, which usually doesn't produce the intended results unless the exact combination is matched.

Use a Helper Column: A practical way to handle this is to use a helper column in your Smartsheet where you transform the multi-select values into a format that can be more easily manipulated with formulas. For example, you can create a formula in each row of the helper column that checks for each potential dealer value and outputs something more manageable (like a binary flag or simplified string).
Adjust the Formula: Once you have the helper column set up, you can adjust your COLLECT formula to check against this helper column instead of the multi-select column directly.

Here's a step-by-step on how to implement this:

Let’s say your multi-select column is {Dealer Name}, and your dealers are "Dealer1", "Dealer2", "Dealer3", etc. You can set up a helper column with a formula like:

=IF(CONTAINS("Dealer1", {Dealer Name}), "1", "") +

IF(CONTAINS("Dealer2", {Dealer Name}), "1", "") +

...

This formula will output a string of 1’s and 0’s for each dealer.

You can then use this helper column to filter your COLLECT formula:

=JOIN(COLLECT({Email Address}, {Helper Column}, "some_condition"), CHAR(10))

Replace "some_condition" with a specific condition that matches your needs (e.g., checking if a particular dealer's flag is set).

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.