How to have automation populate specific contact within column based on state selected in row?

CJ Richards

Scenario: You need alerts and approval requests to go to "contacts in a cell" based on department and state. Is there any way to accomplish this aside from creating a separate contact column for every single state which then has to also be programmed into the automations?

(i.e. Request 1 affects all departments for GA, NC, and SC - I need notifications to go to contacts in "manager/approver" column, but only GA, NC, and SC managers; not all states' managers. It gets more complicated if it only affects marketing and sales departments...now I need the notification or approval to only go to those department managers for those states.) Creating a different contact column for every state and department with different contacts loaded into each AND setting the separate automation logic seemingly would take until 2025 ;)

Leibel S

@CJ Richards

Is there a max qty of approvers?

How do you specify currently the state and or dept for each request?

The overall flow here would be to create a separate sheet that has a list of every state, dept, and manager

Then you have approver fields that lookup this sheet to pull in the correct contacts.

Then the flow uses the approver column to specify who to send it to

CJ Richards

@Leibel S Thanks so much! I think I'm tracking... The state and dept are specified earlier in the respective row (Columns: Change Release, State Affected, Departments affected, etc.) There would likely be 1-2 1st level approvers and 1 final executive approver and some of them will cover entire regions of states. To make sure I'm understanding, you're saying that if I have a separate sheet with state, dept, manager (1st level approver), and exec (final approver), I would then have formulas in a helper column that have an IF function to tell it, "If Sales department and GA (in respective columns), pull contact from (specified column in my contact sheet) - Am I close? Have you successfully had a formula pull in an actual contact into a contact cell before? I'll start playing with this idea and I'm happy to message/chat offline if you are willing!?

Leibel S

@CJ Richards

This is the overall idea.

In your contact sheet create columns with, state, dept, manager, exec.

Then create a column that combines state-dept into 1 cell.

In your request sheet on the contact columns it should have an index match formula using the state-dept from the request to lookup the contact sheet