Auto-assign contact based on department within a form

Options

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!

Tags:

Best Answer

Answers

  • Ryan Philmon
    Options

    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å
    Andrée Starå ✭✭✭✭✭✭
    Options

    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Ryan Philmon
    Options

    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å
    Andrée Starå ✭✭✭✭✭✭
    Options

    @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?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!