Look up and populate user info when a form is submitted?

Brian Curtis

Hello! Is there a way to have Smartsheet recognize a form user's employee ID that's been stored in a preloaded employee-ID spreadsheet and then use that info to autofill the new row in the workspace with the user's corresponding Department ID, Manager Name, email, etc. (assuming all that info is already in the user spreadsheet)? The user is always prompted to enter their employee ID when completing the form.

I'm sure I could enter formulas in each cell of the workspace to point to those cells in the reference worksheet, but I'm looking for a way to have Smartsheet recognize the ID when the form is submitted and auto-complete the info instead of doing it manually. If there's an existing help page on this topic, please link. Thank you!

Kelly Moore

Hi Brian,

Building on Sara good suggestion of column formulas, if I understand correctly, you are asking if a user can submit his/her ID through a form, then have the rest of the employee information gathered from a separate Employee Info Sheet and populate on your current sheet?

If this is correct, the answer is yes! (assuming the Employee ID is part of the information in the Employee Info Sheet).

Assuming you want it to be pulled into the current sheet after form submittal, I offer this.

When I then of 'look up' formulas, two formulas immediately come to mind: VLOOKUP and INDEX/MATCH. Both approaches work well however, personally I use Index/Match [or Index/Collect] because I prefer not to worry with the required table structure of VLOOKUP. I would have these formulas as column formulas.

For each of the columns of info you want to pull in from the Employee ID sheet, this is a possible solution:

For example, for the Dept Manager info needed on the current sheet:

=INDEX({Employee ID sheet Dept Mgr column}, MATCH(EmployeeID@row, {Employee ID sheet Employee ID column}, 0))

Note the Match portion would remain the same for every column on the current sheet, only the lookup term in the Index portion would change. You'll have to change the formula to match your column IDs and your cross-refenced ranges.

I'm not sure I'm answering your question correctly. I hope this helps.

Kelly

Sara Bittner

You could try using the column formula. It would still require you to fill out the formula for each column you would want to pull the information off of, but it would auto-fill the rest of the rows as the form adds.

https://www.smartsheet.com/content-center/product-news/release-notes/column-formulas?iOS=

you just need the right formula type, so if it's vlookup, you would want vlookup(employeeid@row, search table, column number, true)

Kelly Moore

Hi Brian,

Building on Sara good suggestion of column formulas, if I understand correctly, you are asking if a user can submit his/her ID through a form, then have the rest of the employee information gathered from a separate Employee Info Sheet and populate on your current sheet?

If this is correct, the answer is yes! (assuming the Employee ID is part of the information in the Employee Info Sheet).

Assuming you want it to be pulled into the current sheet after form submittal, I offer this.

When I then of 'look up' formulas, two formulas immediately come to mind: VLOOKUP and INDEX/MATCH. Both approaches work well however, personally I use Index/Match [or Index/Collect] because I prefer not to worry with the required table structure of VLOOKUP. I would have these formulas as column formulas.

For each of the columns of info you want to pull in from the Employee ID sheet, this is a possible solution:

For example, for the Dept Manager info needed on the current sheet:

=INDEX({Employee ID sheet Dept Mgr column}, MATCH(EmployeeID@row, {Employee ID sheet Employee ID column}, 0))

Note the Match portion would remain the same for every column on the current sheet, only the lookup term in the Index portion would change. You'll have to change the formula to match your column IDs and your cross-refenced ranges.

I'm not sure I'm answering your question correctly. I hope this helps.

Kelly

Brian Curtis

Looks good, I'll give it a try. Thank you!