Auto-assign contact based on department within a form

kolfinna

Hello!

I have a form for project intake work where the end user has the option to specify in which department they feel their project should reside (for example, DBA versus Phones/Communications, etc.). I'd love to use INDEX/MATCH to auto-assign a PM based on what that department is e.g., DBA = Johnny Appleseed, Phones/Communications = John Doe, and so on--but be able to do so in a way that the end user never has to see that process happen--they just fill out the form, hit submit, and the PM gets notified when there's a new project in their department automatically (of course, based on notification/workflow automation within the underlying sheet).

Thoughts? Or is this overkill? Can I assume a formula on the underlying sheet will propagate even as new rows are entered via the form?

Thanks!

Jenn Hilber

You can create the formula in the sheet your form is entered to. Just be sure that new rows are added below existing rows with the formula. If the formula is inadvertently removed, or a blank row is added to the sheet, the formula will not continue to populate on new form entries.

We use this and it works well as long as the sheet formulas are maintained.

Jenn Hilber

You can create the formula in the sheet your form is entered to. Just be sure that new rows are added below existing rows with the formula. If the formula is inadvertently removed, or a blank row is added to the sheet, the formula will not continue to populate on new form entries.

We use this and it works well as long as the sheet formulas are maintained.

Ryan Philmon

Hi, I have a similar need for my firm and auto-assigning a Task based on certain criteria. Example, I can use the IF formula and it works for one cell and one row, but I have 9 options that I need to automatically assign to someone if their location pops up on the sheet.

EXAMPLE: IF(Country="USA", "ryan@smartsheet.com") works. This would automatically put my name in the column needed when USA is listed in the Country column. How can I add to this formula for other countries?

IF(Country="USA", "ryan@smartsheet.com") OR(IF(Country="Canada", "jane@smartsheet.com") does not work in the sheet. Jane does not automatically pop up in the column I need it to.

Any ideas on how to auto-assign based on Country name?

Thanks!

Andrée Starå

Hi @Ryan Philmon

How many options will there be?

It might be better to create a VLOOKUP or INDEX/MATCH structure instead.

What do you think?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Ryan Philmon

Thanks, Andrée. I have 12 options but some have the same contact assigned to the task.

Example:

USA= Ryan

Canada=Jane

Mexico= Ryan

Jamaica= Ryan

Panama= Jane

Will Index/Match work with this model?

Andrée Starå

@Ryan Philmon

Yes, it would, but I'd recommend a VLOOKUP formula instead and on the same sheet if possible.

Add two new columns, something like this.

Country Helper (add the Countries here)

Name Helper (Add the email address here)

Then you'd add something like this in the Country selection column.

=VLOOKUP(Country@row, [Country Helper]:[Named Helper], 2,false)

Make sense?

Did it work?