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

Joanna Collins
Joanna Collins โœญโœญโœญโœญ

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

Main Sheet

image.png

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

image.png

=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!

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!