Auto fill Dept column based on selection in Category column

Stacy Maplesden

Hi - I'm very new to smartsheet and formulas so I apologize if this is a really basic question. I have been researching how to have my DEPT column autofill the correct department depending on what is filled in the Category column but not having any success. The Category is selected by the end user that is reporting an issue on a form (drop down field). This category column is automatically filled on the sheet when the form is submitted. I'd like the Dept column to fill in the appropriate department that matches the category when submitted. This is the sheet that the form feeds into:

This is my list of categories and associated departments which is on a different sheet, as a reference.

thanks

Bassam Khalil

Hi @Stacy Maplesden

Hope you are fine, you can do that in a very simple way by using the Vloockup function

=VLOOKUP(Dept@row, Category:Dept, 2) when the end user submits his entry the Vloockup automatically will search for you in the helper table and get the correct dept for you.

create a helper table contain the Category and related Dept for each category and you can expand if you have a new entry in the future.

Andrée Starå

Hi @Stacy Maplesden

To add to Bassam's excellent advice/answer.

Here's the formula when the information is on another sheet, and I'd also recommend that you convert the formula so it's a Column Formula. Then it will always auto-fill for each form submission.

=VLOOKUP(CATEGORY@row, {Dept. Reference Sheet, 2, 0)

More information about Column Formulas.

https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas

Did that work?

I hope that helps!

Have a fantastic day & Happy New Year!

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!

Bassam Khalil

Hi @Stacy Maplesden

Hope you are fine, you can do that in a very simple way by using the Vloockup function

=VLOOKUP(Dept@row, Category:Dept, 2) when the end user submits his entry the Vloockup automatically will search for you in the helper table and get the correct dept for you.

create a helper table contain the Category and related Dept for each category and you can expand if you have a new entry in the future.

Andrée Starå

Hi @Stacy Maplesden

To add to Bassam's excellent advice/answer.

Here's the formula when the information is on another sheet, and I'd also recommend that you convert the formula so it's a Column Formula. Then it will always auto-fill for each form submission.

=VLOOKUP(CATEGORY@row, {Dept. Reference Sheet, 2, 0)

More information about Column Formulas.

https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas

Did that work?

I hope that helps!

Have a fantastic day & Happy New Year!

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!

Stacy Maplesden

Thank you both. This worked perfectly and I made it a column formula which now makes sense. :) Much appreciated!

Andrée Starå

@Stacy Maplesden

Excellent!

You're more than welcome!

Bassam Khalil

At your service any time @Stacy Maplesden