Pulling Email Address from Another Sheet but as a Contact
In Sheet 1, a user enters a course code (e.g., BUS 123) in one column. Smartsheet searches for that code in Sheet 2, finds the corresponding program leader's name and e-mail address, then puts them in two cells in Sheet 1.
Now Sheet 1 shows the course code in Column 2, the program leader's name is Column 3, and the email in Column 4. Columns 3 and 4 obviously contain formulas to search Sheet 2.
Column 1 is a drop down in which the user provides an update on the course development---On Track or Not on Track.
I would like Smartsheet to sent an alert if the dropdown is changer to Not on Track. Problem is I can only use an email address in a column set as Contacts, and Contacts columns cannot contain formulas. So while I have the email address in Sheet 1, the alert cannot work.
Is there a way to assign the email address to a cell in Sheet 1 that is a Contact? I cannot manually select the names/emails from a dropdown because I want to automate the course information being put in Sheet 1 so user only has to enter course code.
Any ideas about how to automatically get that program leader's name from Sheet 2 into Sheet 1 so I can use the alerts feature when he Status column is set to Not on Task?
Comments
-
I have a workaround for you (I have to use it in a few sheets), but it is time consuming to set up depending on how many people you are sending emails to.
Basically you would end up setting up an alert for each individual specifying your criteria as If Email column contains johndoe@gmail.com and the status column changes to "Not on Track", send an alert to johndoe@gmail.com. You would then clone the alert and tweak it for the next email address, so on and so forth.
I haven't been able to figure out any cleaner way of doing it yet unfortunately.
-
Hi,
You'll love this!
Formulas in contact columns. It's released now!
https://community.smartsheet.com/announcement/announcing-formula-support-contact-columns
Have a fantastic day!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!