how to get a form on smartsheet to automatically send to a certain manager based on type

Vanessa COWDIN

In a recent video that was posted by John Cicero, he spoke about having the form automatically update the sheet with which manager that intake request should be submitted to. He used a "Match&Index" formula, although he did not show us the steps on which how he did this. I tried to read the help pages on Smartsheet showing how to do this, but it didn't help my situation. Would you kindly show me the baby steps on how Cicero did this?

Thanks

David Joyeuse

Hi @Vanessa COWDIN

The INDEX/MATCH formula will be like this:

=INDEX({Manager Range}, MATCH([Type Request]@row, {Type Request Range}),0)

For this to work, you're gonna need to have a database sheet where you list all the type requests and have the manager you want to link to each type. It's basically a 3 column sheet, where you're not gonna use the Main column (because it's always a type/number column). Make the manager column a contact list type of column and the Type request a single dropdown list column. Have both ranges linked to each column.

Then in your form sheet, make sure the type request column is a single dropdown list that exactly match the items of the helper sheet. Same for the manager column.

Add the formula within the manager column and make sure this column doesn't appear in the form to prevent users for putting names straight forward, which you don't want to happen.

Hope it helped!

Vanessa COWDIN

David Joyeuse

Hi @Vanessa COWDIN

The INDEX/MATCH formula will be like this:

=INDEX({Manager Range}, MATCH([Type Request]@row, {Type Request Range}),0)

For this to work, you're gonna need to have a database sheet where you list all the type requests and have the manager you want to link to each type. It's basically a 3 column sheet, where you're not gonna use the Main column (because it's always a type/number column). Make the manager column a contact list type of column and the Type request a single dropdown list column. Have both ranges linked to each column.

Then in your form sheet, make sure the type request column is a single dropdown list that exactly match the items of the helper sheet. Same for the manager column.

Add the formula within the manager column and make sure this column doesn't appear in the form to prevent users for putting names straight forward, which you don't want to happen.

Hope it helped!