Open Discussion: Formula to Populate Multiple Contacts in a Single Cell?

Paul Newcome
Paul Newcome ✭✭✭✭✭✭
edited 08/19/21 in Formulas and Functions

Alright... So there have been a lot of questions about using a formula to populate multiple usable contacts into a single cell, and it has always been said that it can't be done.


Well here is a solution that will start to fix that. I was presented with a challenge of populating multiple contacts into a single cell based on a multi-select dropdown column.

Reference Sheet:

Primary.....Contact

a................John Smith

b................Jane Doe

c................Paul Newcome


Working Sheet:

Multi-Select.........Output

a................................f

a b.............................f

a b c..........................f


Formula:

=JOIN(INDEX(COLLECT({Reference Sheet Contact Column}, {Reference Sheet Primary Column}, HAS([Multi-Select]@row, @cell)), 0), ", ")


Output:

Actually output the correct contacts as USABLE contacts into each of the single cells!!!!!! 🤯


Shout-outs:

@Susan Vieira for letting me highjack one of her posts to learn more about using 0 (zero) as the row number in an INDEX function.

@Genevieve P. For the brilliant idea of using 0 (zero) as the row number in an INDEX function.

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!