Populating other cells with combo box selection

[Shevlin Ryan]

On Sheet 1 I have the following data:

A B C D
1 Part CaseQty CasePrice UnitPrice
2 Nut 100 $5.00 =C2/B2
3 Bolt 200 $6.00 =C3/B3
4 Screw 300 $7.00 =C4/B4
5 Washer 400 $8.00 =C5/B5

On Sheet 2 I have a combo box to select from column A of Sheet 1 and
stores the selection in cell A1 of Sheet 2. I would also like cells B1,
C1, and D1 of Sheet 2 to be populated by the rest of the selected row's
information. So if I choose Washer in the combo box, then Sheet 2 will
have:
A B C D
1 Washer 400 $8.00 $0.02

I can get Washer into A1, but it's the other three cells that have me
stumped. Once I've made my selection, how to I tell either Excel or VB
which row on Sheet 1 has been chosen? Once I get that, I think I can
handle the rest. I'm using Excel 97 and have done similar things in
Access, but don't have much Excel experience.

Thanks in advance.

 

[Bob Phillips]

B1: = VLOOKUP(A1,Sheet1!$A$1:$D$20,2,False)
C1: = VLOOKUP(A1,Sheet1!$A$1:$D$20,3,False)
D1: = VLOOKUP(A1,Sheet1!$A$1:$D$20,4,False)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Shevlin Ryan]

That did it, Bob. Thanks again.