Using Join, collect, and contains with a multiselect column
Hello,
I'm trying to see if I can extract a location's email address into a multi contact column but I seem to be hitting a bit of a wall when someone selects two or more entries in their location. The contact emails are listed on a separate sheet but both sheets share the same location naming.
=JOIN(COLLECT({Location Email}, {Location}, CONTAINS(Location@row, @cell)), CHAR(10))
The formula works when there is an individual location added to the location column in the sheet I'm working with but as soon as you select a second location, I get a blank.
Anyone have any thoughts?
Answers
-
Hi @kioshi43
The Location@row portion of your formula that references a cell with a value to search for in your {Location} cross-sheet reference would need to be a single selection.
There currently isn't a way to parse out multiple selections in one cell by referring to it and then search for each of the values individually in another column; you would need each value to be split out individually.
You could potentially search for exact matches... for example if your Location@row had 2 locations selected, then you could search the {Location} column for an exact match. This would skip over individual selections and search for the unique combination of just these two locations, no more, no less. Does that make sense?
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!