Sorting of Contact list with Allow Multiple Contacts in a cell
I have a sheet that allows multiple contacts in a cell. I was trying to use a formula to pull the persons actual name from another sheet using INDEX from the contact list in that cell. My other sheet contains all of the email addresses in one column, their actual name in another and department in another. This makes it very difficult to index, and I'm not sure there is any way to index from multiple entries (if so that would solve my issue). But the first person in the contact should be the owner of the process, and I can pull that email to index. My issue is, once a person selects their email, and then perhaps adds more people, the cell sorts alphabetically, which then makes my index select the wrong person and department for responsible (if their name is sorted to 2nd, 3rd ... ). Is there anyway to tell the cell not to sort the email address?
Example formula: =IFERROR(IF(FIND(",", JOIN(Email@row)) > 1, INDEX({Service Team Roster Range 4}, MATCH((LEFT((JOIN(Email@row)), FIND(",", (JOIN(Email@row))) - 1)), {Service Team Roster Range 1}, 0)), INDEX({Service Team Roster Range 4}, MATCH(Email@row, {Service Team Roster Range 1}, 0))), "N/A")
This Joins the contact list cell and looks to see if there is more than 1 email listed. If so, it pulls the first email and looks up the persons name (and department depending on the index). If only one contact is listed, it simply pulls the data using the one email address.
As I said, since it is pulling the first email listed in the contact cell, it may pull the wrong persons information into this cell because once you enter the contacts and continue, it sorts the contact list.
Example: BB is the owner and AA is the stakeholder. When you select BB them AA it looks right until you continue. SmartSheet sorts the cell and now it is listed as AA@xxx.com, BB@xxx.com.
Answers
-
You are going to need to use a separate column for the email you want to IDNEX against and then another column for the rest of the emails.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
I wanted to avoid that, but certainly is a easy way to avoid the hassle. Thank you.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives