Cross-Reference Formula with Multi-Select Drop-Down Box

I have two columns in my sheet:

SHEET 1 "Program Development"
———————————————
COLUMN 1 (AU/Partner@row): Drop-down box, multi-select acronyms for academic units

COLUMN 2 ([AU Dean]@row): Contact column that should include the email address of the academic dean in each selected academic unit in COLUMN 1

SHEET 2 "Team Directory"
———————————-

COLUMN 1 ([AU/Partner]@row): Each row has different academic unit acronym

COLUMN 2 (Email@row): Contact column with email address (allows multiple contacts)

I've successfully used cross-reference formulas before when I was essentially doing a one-to-one match.:

=INDEX({TeamDirectory-Email}, MATCH([Designer Name]@row, {TeamDirectory-Title}, 0))

Now, a cell in Sheet 1 Column 1 may have more than one acronym, which means there would be more than one contacts (emails) in Sheet 1 Column 2. I don't know how to write the cross-reference formula to include in Sheet 1 Column 2, so it looks up each acronym listed (could 1, 2, 3, 4, or even 5 acronyms), and populate Sheet 1 Column 2 with all of those contacts.

I was playing around with something like this:

=JOIN(COLLECT({TeamDirectory-Email}, {TeamDirectory-Title}, HAS(@cell, SPLIT ([AU/Partner]@row, ","))), ", ")

It didn't work. Any thoughts?

Find more posts tagged with

Accepted answers

All comments

Georgie

Hi @aschneiderheinze1025,

I believe a JOIN(COLLECT) formula is the right way to go. I found a similar thread here: Index(Collection formula for dropdown field with multiple entries. I’d try the following in your AU Dean column:

=JOIN(COLLECT({directory-email}, {directory AU Partner}, HAS([AU/Partner]@row, @cell)), ", ")

The {directory-email} reference is the entire email@row column in the directory sheet (I believe that’s how your {TeamDirectory-Email} reference is already set up), and the {directory AU Partner} reference is the entire AU/Partner column in the directory sheet - you’ll need to adjust the formula to make sure it references the correct ranges in your sheets.

Does that work for you? If not, could you provide some screenshots of your two sheets and let us know what’s happening when you try the formula - are you getting an error message, or is it pulling incorrect values?

Thanks,

Georgie

aschneiderheinze1025

That works—-at least in pulling the correct name(s) based on the values selected in the dropdown! However, it is pulling the name, not the email. The cell formula in Sheet 1 Column 2 is reference a column in Sheet 2 that is a Contact column:

=JOIN(COLLECT({TeamDirectory-Email}, {TeamDirectory-Title}, HAS([AU/Partner]@row, @cell)), ", ")

Sheet 2. Team Directory

So, if a user selects CALS and PACE in Column 1 of Sheet 1, in Column 2 of Sheet 1, it would pull the email addresses of the two contacts associated with those acronyms in Sheet 2. In other words, is there a way to pull the email part of the contact, not the name (so bbname@abc.edu, rather than Bob Barker)? What I really want is the full Contact to show up in Sheet 1, just as it appears in the Email column in Sheet 2 (so not just the email, but the name and email).

Sheet 1 Columns 1 and 2

Georgie

Hi @aschneiderheinze1025,

It’s possible to extract the email address from the contact list column (take a look at this thread) and then update your formula to use that column so that it pulls in the email addresses rather than the names.

So, you could do the following:

In your directory sheet, EITHER change the Email column to a Text/Number column, OR create another contact list column and populate it with the same values, then change that column to a Text/Number column. This will show the name and email address in the format: Jane Doe <jane.doe@email.com>.
In another column in the directory sheet (you may want to name this “Email only” or similar), use a formula to extract the email address from the column you converted to Text/Number. Your formula will be similar to below (if you created a new contact column and converted that to Text/Number, you’ll need to edit the formula to reference that column in all 4 instances):
1. =MID(Email@row, FIND("<", Email@row) + 1, FIND(">", Email@row) - FIND("<", Email@row) - 1)
Convert the above to a column formula (right-click the cell containing the formula and select Convert to Column Formula).
In sheet 1, adjust the formula I previously provided to reference the "Email only" column - so you can either update your existing {TeamDirectory-Email} reference so that it looks at the new “Email only” column, or you can create a new reference pointing to that column.

Hope that helps!

Georgie

aschneiderheinze1025

Thanks @Georgie. That sounds like a logical approach. I'm guessing the only down-side is there is a manual component.: copying/pasting the content in the Contacts Column, or manually creating the Columns column and changing it to a Text/Number column.

Is this correct? There is no way to automate this within Smartsheet? I am doing this as part of a consulting project, and I am trying to keep the amount of manual work to a minimal.

I also tested putting just the email address in Sheet 2, and it extracts the email address via the formula above in Sheet 1, but it only displays it as an email address, not as a Contact. (The column is a Contact column, and the Contact exists. It's just showing the e-mail address. Was hoping it would show it as a Contact in Sheet 1.)\

Maybe—-related to this, if I extract the Names into Sheet 1 (into a Helper column), is there a way to create a Contact (in the Contact column) based on the name(s) in the Helper column? Or, if it extracts an actual email address (into the Helper column), put a formula in the Name column that takes the email and makes it an actual Contact?

Georgie

Hi @aschneiderheinze1025,

That’s right, currently there’s no way to automate this in Smartsheet.

You can pull through single usable contacts from one cell to another as long as both columns are single-select contact columns, but it’s not possible to combine contacts using formulas and have the result displayed as usable contacts. I found the following related thread: Join/Collect , Index/Collect Multi-Select Column with Contact List return value.

Please add your vote to this product idea - you’ll then receive updates when the status of the idea changes: Combine Contacts from multiple columns into a single cell.

Thanks,

Georgie

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.