Automatic population of an excel sheet

0
votes

I am looking to make a form in which you can pull down a menu and have fields populate based on your selection. On my excel workbook I have Sheet 1 which is the menu and Sheet 2 which has the data.

Sheet 1 is laid out as such

Item Code   Description    Wholesale Price     Retail Price

Sheet 2 is:

00001       Beans           0.45                0.67

I have setup a drop down list in A1 (Item Code) to reference the item code range on Sheet 2 but what I want to happen after I select the item code is for the rest of the information to populate. I have come close using VLOOKUP but I just don't seem to be able to nail it down.

Any suggestions?

2
excelvlookupformulas

2 Answers

1
votes

In Sheet1:

B2 =VLOOKUP(A2,Sheet2!$A$1:$D$10,2,FALSE)
C2 =VLOOKUP(A2,Sheet2!$A$1:$D$10,3,FALSE)
D2 =VLOOKUP(A2,Sheet2!$A$1:$D$10,4,FALSE)

Where:

  • Sheet2!$A$1:$D$10 is your reference table in Sheet2
  • In Sheet1 I assume you have a header row (hence A2, B2, C2, D2)
1
votes

Sheet 1: Sheet 1

Formula:

=VLOOKUP($A2,Sheet2!$A:$D,2,FALSE)

Where

  • A2 is the range of the Item Code
  • Sheet2!$A:$D is the 4 columns to look in for data
  • 2, 3 and 4 are the columns (within the above 4) of your desired data.
  • FALSE requires it to be an exact match

Sheet 2:

Sheet 2

Result in sheet 1:

Result

Note: I've formatted the A columns as text to preserve the leading zeros, hence the little green triangles in the cell corners.