Join/Collect , Index/Collect Multi-Select Column with Contact List return value

Joanna Collins

I am trying to update contacts on my main sheet based on values in a multi select column.

Main Sheet

Based on The selections in the Product Groups Affected I want to update the contacts in the Contact List Column based on my contacts in sheet 2

=JOIN(DISTINCT(COLLECT({QAlertAreas.Contacts Range 1}, {QAlertAreas.Contacts Range 2}, HAS([Product Groups Affected]@row, @cell)), ",")) gives me an #INVALID DATA TYPE ERROR

=INDEX(COLLECT({QAlertAreas.Contacts Range 1}, {QAlertAreas.Contacts Range 2}, CONTAINS([Product Groups Affected]@row, @cell)), 1) gives me an #INVALID VALUE ERROR

Both of my contact columns are set up as Contact Columns and to allow multiple contacts per list. Appreciate any help!

Thanks!

Paul Newcome

There is currently no way to use a formula to output multiple usable contacts within a single contact type cell.

Paul Newcome

There is currently no way to use a formula to output multiple usable contacts within a single contact type cell.

Joanna Collins

Thank you for the response. Now to get creative...

Eric Gonzales

https://community.smartsheet.com/discussion/comment/339684#Comment_339684

If you do - let us know how you do it!

Jess D

Is there a way to use a formula to output a single contact? My formula is pulling from a contact field on Sheet 1 and the output field is also contact type but the result is … not the contact. The name is correct but their contact information did not flow and the column is giving an error because of the formatting.

Genevieve P.

Hi @Jess D

Yes, you can surface a single contact from one cell into another, as long as both columns are Contact columns. Can you paste what formula you're using?

Jess D

Hi Genevieve, thanks for your help.

I can confirm that both the originating and destination fields are of the column type Contact List.

The formula I am using is:

=IFERROR(INDEX(COLLECT({Primary TAM Assignments TAM}, {Primary TAM Assignments Client}, Client@row), 1), "Missing TAM")

Perhaps I am going about this the wrong way. I have four primary sheets (one per manager) that includes the client, the manager, and the TAM assigned - as well as lots of other information. The destination sheet is a health indicator that the TAM would complete. I haven't thought through if there is a way to automate adding the client name from the primary sheets to the health sheet, since I don't want to the whole row from the manager sheet. I am currently using the client name as the criterion range to populate both the TAM and manager (hopefully using a nested INDEX/COLLECT). The TAM field is contact list type so that reminders can be automated to update the client health.

I appreciate your perspective both on the formula as well as if I should pursue the end goal with another approach.

Genevieve P.

Hi @Jess D

That formula should work without an issue!

Here is a screen capture of how it should show up:

HOWEVER! Since formulas cannot combine contacts, if the source Contact Column is enabled to allow multiple contacts, it will pull the value through as plain text.

Can you make sure that this is un-checked in both columns:

Thanks!
Genevieve