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?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!