Pulling Email Address from Another Sheet but as a Contact

Art Schneiderheinze

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?

Paul Newcome

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.