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. :)

John Knipper

Am I correct that: 1) column is created as Text/Number, 2) the formula is entered to pull the email address, 3) the formula is changed to a column formula, and finally, 4) the column is changed to a Contact type?

jodyh

Hi John,

I think you could use VLOOKUP here as well, just cross-referencing sheets. Check out this thread

https://community.smartsheet.com/discussion/40081/how-to-use-vlookup-for-other-sheets

Specifically, Mike Wilday's comment (third one down) explains it. So long as your reference sheet had territory, name and email address, I think you can finagle it. Once the Project sheet is set with this info, you could set the Automation.

:) Jody

John Knipper

I can use vlookup to pull in the email address. My issue is that the Automation feature to 'Alert Someone' seems to need a value from a Contact column. I can't find a way to enter a formula into a cell in a Contact column.

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. :)

John Knipper

Am I correct that: 1) column is created as Text/Number, 2) the formula is entered to pull the email address, 3) the formula is changed to a column formula, and finally, 4) the column is changed to a Contact type?