Autopopulation

Michael Hair

Is there way for when I choose a customer from either a dropdown or contact list in a form, that I could have their customer ID autopopulate into a separate field? For example, if we choose a customer name in the form, their customer ID would autopopulate in a column in the sheet and possibly in the form itself.

Matt Johnson

Thanks @Michael Hair

The vlookup is a 4 part function:

Search Value: This is the Customer Column on the Test sheet.

Lookup Table: This is the range of the 2 columns (Customer ID and Customer Name) on the Customer Info sheet.

Column Number: This is the count of how many columns to the right you want the function to count from the lookup value to the return value. In this case you need to put the Customer ID column to the right of the Customer Name and your Column number is 2. If column6 was inbetween the ID and the Name column then your number would be 3. I hope I explained that correctly.

Match: Here you choose true or false. I always use false. You could search around for the explanation but I usually go with false and never have issues.

I hope that helps.

Here is the formula as well. You might have to type it in to get the cross sheet reference to work.

=VLOOKUP(Customer@row, {Customer Info Range 1}, 2, false)

Matt Johnson

Hello again @Michael Hair

You could have the customer ID drop into the sheet by using Datamesh or a vlookup if you had all Customers and IDs on a separate sheet. It couldn't populate in realtime on the form itself but it could automatically drop onto the sheet after the user submitted the form. I would suggest locking the column and adding a description that says "auto populate" or something to that affect so users of the sheet will know that the ID comes from another source.

Michael Hair

Hey @Matt Johnson

Thank you for the helpful response. Do you know how the vlookup formula works? When I try to use it to reference another sheet, I get a result that says #incorrect argument. This sheet that I am referencing has the customer information including the customer ID.

Matt Johnson

@Michael Hair

Please post the formula you are using here in the thread and let's take a look.

Michael Hair

@Matt Johnson

Here is the formula:

Here is the sheet I'm referencing:

Matt Johnson

Thanks @Michael Hair

The vlookup is a 4 part function:

Search Value: This is the Customer Column on the Test sheet.

Lookup Table: This is the range of the 2 columns (Customer ID and Customer Name) on the Customer Info sheet.

Column Number: This is the count of how many columns to the right you want the function to count from the lookup value to the return value. In this case you need to put the Customer ID column to the right of the Customer Name and your Column number is 2. If column6 was inbetween the ID and the Name column then your number would be 3. I hope I explained that correctly.

Match: Here you choose true or false. I always use false. You could search around for the explanation but I usually go with false and never have issues.

I hope that helps.

Here is the formula as well. You might have to type it in to get the cross sheet reference to work.

=VLOOKUP(Customer@row, {Customer Info Range 1}, 2, false)

Michael Hair

@Matt Johnson

That works! Thanks for the help.