Form submission automation

Nathan Umbriac

Hello,

I have created a form where users can select a category and submit a question. Once they select a category and submit the form, I'd like my sheet to automatically notify a subject matter expert matched to that category on a separate sheet. What is the best way to accomplish this?

Thanks!

Nathan

Katy H

A couple of ways to pull this off. One is to create an automation for each subject matter expert. Using the categories to define who gets the notification.

The other is to have a contact column that is controlled by a formula that pulls contact information for each SME based on category, and a single automation that will notify the SME in the contact column no matter the category. If you go the formula route I would recommend building a second sheet with the names of the SMEs and their associated categories.

Nathan Umbriac

Thank you, @Katy H . I have a separate SME listing sheet. Is there an easy way to link/automate the form submission directly to that sheet? That way, if SMEs change and the listing is updated, there's no additional linking needed.

Katy H

Yes, the best way to do this would be with an INDEX/MATCH formula.

The formula would look something like this:

=INDEX({Column with SME Contact Info}, MATCH(Category@row, {Category Column in SME Sheet}, 0))

Then you can use a standard automation to notify the person in the contact column that a new form has been submitted.

Apologies for the delay! I missed the notification. Hopefully this makes sense. Happy to clarify with more information.

Nathan Umbriac

It seems to be working perfectly. Thanks @Katy H !