Contact value based on formula

John Knipper

I'm using Smartsheet (named Projects) to track progress on some projects. One of the columns in the sheet contains a Territory #. There is a Sales Manager assigned to each Territory which is kept in a separate smartsheet with one column (text/number) containing the Territory # and a second column (contact) containing the email address for the applicable Sales Manager.

A form is being used to enter rows in the Projects sheet. In order to reduce input in the form, I'd like to use a formula in the Projects sheet to pull the email address from the separate sheet based on the Territory entered in the form. (I pull other data from similar sheets using Vlookup and/or Index/Match formulae.)

I'd like to create an automation to send an email to the Sales Manager with a notification about the project. How can I accomplish this? It appears the automation requires a Contact column to contain the email address. How I can use the email address being pulled from the other sheet with a formula? Is there a way to use a formula in a Contact column?

Thank you, John

jodyh

Thanks, John. I guess I'm misunderstanding how you have everything connected. For an example project sheet, I have -

The Assignee Email Address column populates based on what is in the Assignee column (matched to my other sheet)- it's using this column formula (below) to pull in an email address from another sheet (both columns are Contact columns):

=VLOOKUP(Assignee@row, {Contact List Range 4}, 2, false)

My contact sheet has name and email address (but you could do the same thing with territory and email address). Then I have Automation set to alert-

I tested the alert with my email address.

Am I getting closer? Sorry....it's tricky with these threads not being able to see all the sheets involved. :)