Populating other cells with combo box selection
 

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.

Populating other cells with combo box selection
 

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" wrote in message
...
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.

Populating other cells with combo box selection
 

That did it, Bob. Thanks again.

Bob Phillips wrote:

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)