autofill multiple cells with details based off a customer code.

Question

What I am trying to do is save myself some time from entering 20 cells worth by entering the customer code.

Example:

customer address phone contact

peg 12 fir street 555 555555 Al

Basically, I have to enter some of the same names on a shipping document everyday including addresses and all the details. I want to add the address and phone and contact of customer Peg,when I enter in the customer code peg.

I am way out of my depth...but have some idea its something to do with either a list or a reference to another page.

Can anyone help me with this?

Answer 1

You could create a Worksheet of your Customer's information (let's call it Customers) and then have VLOOKUP formulas in your Shipping Document that pull the customer information.

Customer Worksheet:

Code        Name            Address
PEG     Peggy Olson     10 E Madison Ave., Suite 205
DRP     Don Draper      10 E Madison Ave., Suite 100

Shipping Details Worksheet

This would have a formula for each column you want to lookup. The formula would be something like:

=VLOOKUP(A2,Customers!A2:C5000,2,FALSE)

This formula says: using the value in A2, look up the table in Customers from A2 to C5000 and return the 2nd column. The FALSE says that the customer code must be an exact match.

Now you should be able to just type the customer code in your document and it will automatically lookup the other values (i.e. Name & Address).

If you do not want to see the "#N/A" for those rows that don't have a customer code (or where one cannot be found) you can update your formula to display a blank when there is an error.

In otherwords:

=IF(ISERROR(VLOOKUP(A2,Customers!A2:C5000,2,FALSE)),"",VLOOKUP(A2,Customers!A2:C5000,2,FALSE))

Hope that helps! Some pictures are attached to showcase what it would look like. The highlighted section is automatically populated.