Display an email address based on a name selected in a separate contact field
Good Morning,
I have a sheet called "RFIs" used to track requests for information. I also have a contact list populated with my requestor names and email addresses.
The RFI sheet has a field called requestor it is a single select drop down field pulled from the Contact list. The Requestor field displays the name of a person originating the request.
I would like to display the requestors eMail address in a separate field called eMail. I would like that to automatically fill after the Requestor has been selected. Making it a hyper link would be absolutely awesome.
Thank You
Best Answer
-
Your helper sheet has 3 columns - Name, Email, and Phone - populated with that info for each person...
Then on your main sheet, you have the same three columns. On this sheet, the Email column can be a Contact List type column, which would enable you to run automations like alerts to the emails in that column.
For Email:
=INDEX({Reference to Helper sheet Email column}, MATCH(Name@row, {Reference to helper sheet name column}, 0))
For Phone:
=INDEX({Reference to Helper sheet Phone column}, MATCH(Name@row, {Reference to helper sheet name column}, 0))
As long as your names match between the main and helper sheets, it will pull the correct email and phone each time you select the name on your main sheet.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
You could do this with a helper sheet. Create two columns in your helper sheet, one for Name, the other for Email, and populate with same from your contact list.
In your RFIs sheet, in the eMail field, use INDEX/MATCH formula:
=INDEX({Helper Sheet Email}, MATCH(Name@row, {Helper Sheet Name}, 0)
When creating the index match formula, follow the on screen prompts to create the references to your helper sheets.
This formula will get the Email value from the helper sheet row where the Name is the same as the Name on the RFIs sheet.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Good afternoon Jeff,
I've been trying to get this formula to work as well with little success. I have three columns on a helper sheet, first name, last name and email address. I'd like the email address to automatically populate a cell when the last name on the sheet matches the last name on the helper sheet. Any help would be greatly appreciated. This is my first post so, if I can share the sheets I'd be happy to. Thanks!
-
I am looking to do the same.
Select a name in a name column and then have it auto populate their email and phone number in the next two columns.
I understand the cheater sheet and have no issues creating, I'm trying to figure out the formula.
If there is any assistance I would greatly appreciate the help. Thanks
-
Your helper sheet has 3 columns - Name, Email, and Phone - populated with that info for each person...
Then on your main sheet, you have the same three columns. On this sheet, the Email column can be a Contact List type column, which would enable you to run automations like alerts to the emails in that column.
For Email:
=INDEX({Reference to Helper sheet Email column}, MATCH(Name@row, {Reference to helper sheet name column}, 0))
For Phone:
=INDEX({Reference to Helper sheet Phone column}, MATCH(Name@row, {Reference to helper sheet name column}, 0))
As long as your names match between the main and helper sheets, it will pull the correct email and phone each time you select the name on your main sheet.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Jeff - THANK YOU VERY MUCH - this has been a life saver and I truly appreciate your formulas !!
This is the best community to help everyone out!
Thank you again!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!