Automate message to a contact on a separate sheet

MichelleN

I have a complex sheet of information about people who finish a certification. This sheet includes their department.

On a separate sheet, I"ve created a contact list of their managers including a Contact column and the department. Sometimes units have up to 3 managers for the same department.

When the person in the first sheet finishes the certification, I want to set up an automation to send an email to the manager(s) to the email in their Contact column. If I cannot do this directly, then I would like to add a column to the first (certification) sheet that uses a formula to pull in the manager name from the Manager Contact list sheet matched by "Department" which uses the same drop-down values on each sheet, but which are not exclusive values (many will have that department on the certification sheet; the manager contact list will have 1-3 values for the department).

How would you do this? Thanks!

Isaac A.

Hi @MichelleN!

Yes, this is definitely possible with Smartsheet's capabilities! You can use a combination of cross-sheet references with formulas and automated workflows to send notifications to the managers based on department. However, since I don’t have access to your sheet's structure, it's a bit tricky to provide exact formulas and automation steps that would fit your needs.

As a starting point, I recommend checking out these helpful articles to guide you through setting up the solution:

• Create cross-sheet references
• Cross-sheet formulas
• Formula combinations for cross-sheet references
• Automate processes with workflows
• Send automated email notifications

If you run into any issues or have further questions, feel free to reply back and include screenshots of your sheet's basic structure and the relevant components needed for creating the formulas and workflows.

I hope this helps!

Cheers,
Isaac.

MichelleN

Thank you for your response. I appreciate those suggestions. I actually do use tons of automations including email automations and tons of pretty complex formulas. I just don't understand how to go about it here. The SPOT smartsheet has many more columns tracking certification. I've blurred everyone's personal ID and email information here. The Department field is academic dept and is a pull down menu with a lot of choices. Departments are within Divisions and the division is also a pull down menu with about 8 choices. They are the same pull down list on both sheets. There are 1-3 managers for each department.

What is the best workflow? Is it 1. use a formula to pull the (1-3) names of the managers into a column in the main sheet and then build the automation in the main (SPOT) sheet to email those managers based on the fill-in of the certificaton date and certification complete checked or 2. is there a way to put the automation on the Contacts sheet where, when the certification date is filled in and certification complete checked, it looks at the "dept" in that row on the SPOT sheet, then sends an email to the contact(s) in the contacts smartsheet?

If it is #1, what formula do I use to pull in multiple names? COLLECT? INDEX? VLOOKUP? I have tried those or combinations of those and I am not getting it right. And if there are multiple names in a cell, will automation send to all of them, or do I have to make more columns? I hope that helps articulate what I'm trying to accomplish more clearly. Thanks!

contacts smartsheet.png

spot smartsheet.png

Toufong Vang

Hi, @MichelleN , you can use JOIN( COLLECT() ) to collect the email addresses for the department managers into a Managers cell/column: for example, bugs@acme.com;daffy@acme.com;elmer@acme.com. Then parse for each of the contacts into the Contact1, Contact2, and Contact3 columns.

Contact1

=IF(FIND(";", Managers@row) = 0, Managers@row, LEFT(Managers@row, FIND(";", Managers@row) - 1))

Contact2

=IF(LEN(Managers@row) = LEN([Contact1]@row), "", IF(FIND("*", SUBSTITUTE(Managers@row, ";", "*", 2)) = 0, MID(Managers@row, FIND(";", Managers@row) + 1, 100), MID(Managers@row, FIND(";", Managers@row) + 1, FIND("*", SUBSTITUTE(Managers@row, ";", "*", 2)) - LEN([Contact1]@row) - 2)))

Contact3

=IF(FIND("*", SUBSTITUTE(Managers@row, ";", "*", 2)) = 0, "", MID(Managers@row, FIND("*", SUBSTITUTE(Managers@row, ";", "*", 2)) + 1, 100))

EXPLANATION
SUBSTITUTE( , , , nth)
FIND("*", SUBSTITUTE(Managers@row, ";", "*", 2))
Essentially, you're replacing the nth instance of ";" with "*" and then using FIND() to locate the position of the asterisk. In this case you're trying to find the position/location of 2nd instance of ";" in bugs@acme.com;daffy@acme.com;elmer@acme.com (meaning there are 3 contacts).

Having found the 3 contacts, you can use them in your workflow automation:

Hope this was helpful!