JOIN DISTINCT COLLECT or JOIN INDEX COLLECT w/ Multi Dropdown List

Hi,

I want to return the "Assigned to" contact for the "Assignee Role(s)" selected in a multi-select dropdown list.

Here are the formulas I've tried so far:

JOIN(DISTINCT(COLLECT({Project Settings - Team Members}, {Project Settings - Contact Role}, CONTAINS([Assignee Role]@row, @cell))), ", "))

JOIN(INDEX(COLLECT({Project Settings - Team Members}, {Project Settings - Contact Role}, CONTAINS([Assignee Role]@row, @cell)), 1), ", "))

Find more posts tagged with

Construction

Sheets

Formulas

Support

Project Management

Accepted answers

All comments

Paul Newcome

We currently cannot use a formula to string together multiple usable contacts within the same cell. We can pull the contacts together as a text string, but you will not be able to use it as a contact type data for filters, automations, etc.

ShelbyWarren

@Paul Newcome I'm ok with it not returning the actual contact info. As long as the text string of the name returns - it's good.

Paul Newcome

Try a HAS function instead of CONTAINS.

ShelbyWarren

The HAS function seems to only return the first value it finds. However, in this scenario, there could be multiple Roles selected, which would require multiple names in the contact column.

Paul Newcome

Can you post the formula with the HAS function?

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.