Help with Automation Workflow based on Large Email List for Condition

b.adams

Hi everyone,

I've set up a smartsheet with a form for our employees to sign up for an internal training program. One of the form fields asks for their manager's email, and I need to notify different internal contacts depending on which group that manager falls into.

Here's where I'm stuck:

• I have two separate lists of manager emails, one with 685 emails and another with over 2,000 emails.
• I want to set up an automation for the below:
  • If the "Manager Email" field contains an email from List A - notify Person A
  • If it contains an email from list B - notify Person B.

I know how to build the automation logic, but manually inputting thousands of email addresses as condition values isn't ideal.

Is there any way to bulk upload or reference these lists in the automation rule instead of entering them one-by-one? Or is there a smarter way to structure this?

Any ideas or creative workarounds are appreciated!

John_Foster

Hi @b.adams

We have a similar requirement and have created a helper sheet. The helper sheet contains the list of email addresses and a lookup value, we then use the index & match functions to return the corresponding contact email address or addresses.

Happy to help further if you would like any more details.

Thanks,
John

John_Foster

Hi @b.adams

We have a similar requirement and have created a helper sheet. The helper sheet contains the list of email addresses and a lookup value, we then use the index & match functions to return the corresponding contact email address or addresses.

Happy to help further if you would like any more details.

Thanks,
John

b.adams

https://community.smartsheet.com/discussion/comment/479540#Comment_479540

Thank you! I'm not familiar with the INDEX and MATCH functions. Would you mind providing more context on how you used these for your automation conditions? Or did you use this instead of those or as a first step before the automation?

Heidi Truitt

@b.adams

=IF(INDEX({Manager Name Range}, MATCH([Person]@row, {Person Range}, 0)) = "YOU", INDEX({Manager Email Range}, MATCH([Person]@row, {Person Range}, 0)), "")

Then you are able to add your automations from the destination sheet and use the pre populated email from the helper sheet. ie

Helper sheet contains a columns for the "Person", "Manger Name" and "Manager Email.

The destination sheet would need to contain the "Person" column and the "Manager Email" column. You would add the formula above and create the automation to notify the "Manager Email" cell without needing to manually add

Hope that helps!

b.adams

https://community.smartsheet.com/discussion/comment/479540#Comment_479540

I was able to figure out how to use INDEX, MATCH and it worked perfectly for what I needed! Thanks so much!

b.adams

https://community.smartsheet.com/discussion/comment/479594#Comment_479594

Thank you! I was able to use this formula as the solution: =INDEX({Helper Sheet Range 3}, MATCH([Manager Email]@row, {Helper Sheet Range 2}, 0))

Heidi Truitt

@b.adams I am glad it made sense!