list lookup

[Quark]

I have a list in 3 columns, SKU, Description, Price.
On another page will be an invoice where you select the item (SKU ), from a
drop down list, then all the other info relevant to it; the description and
price will fill in the next two columns. I can get the drop down selection
cell to populate with all the correct info, but I can't figure out how to
get the other cells to change when I select the SKU in the first cell. I
know I have to use the Index or Lookup functions, but I can't seem to quite
get it right. Any help would be appreciated.

 

[chadt74]

I have a list in 3 columns, SKU, Description, Price.
On another page will be an invoice where you select the item (SKU ), from a
drop down list, then all the other info relevant to it; the description and
price will fill in the next two columns. I can get the drop down selection
cell to populate with all the correct info, but I can't figure out how to
get the other cells to change when I select the SKU in the first cell. I
know I have to use the Index or Lookup functions, but I can't seem to quite
get it right. Any help would be appreciated.

Vlookup would probably be the easiest way to do this...

In BOOK3 on Sheet1 you have :
A B C
1 SKU Des Price
2
3 2522 1 1/4 in Screw $1.25
4 7777 3/4 Washer $3.25
5 9999 Hex Nut $5.55

On your INVOICE page you have :

A B C
1 INVOICE
2
3 SKU Description Price
4 2522 1 1/4 in Screw $1.25

You said you have the drop down box working in the SKU column so I'll
skip that.

Open both worksheets

Under description type "=VLOOKUP(A4," and then go to BOOK3 where you
have the SKU/Des/Price table and highlight the whole table. In this
case it would be A3:C5. Continue by typing ",2,false)"

That should pull the description. Put the same exact formula under
price, except change the ,2, to ,3, (you're telling the formula to
pull the 3rd column instead of the 2nd). And you should be all set.
End formula should be

=VLOOKUP(A4,[Book3]Sheet1!$A$3:$C$5,3,FALSE) for the price.

you will get a N/A if the formula can not pick up the sku and you can
avoid that by putting an IF statement in there like this

=IF(ISERROR(VLOOKUP(A4,[Book3]Sheet1!$A$3:$C$5,3,FALSE))=TRUE,0,VLOOKUP(A4,[Book3]Sheet1!$A$3:$C$5,3,FALSE))

Hope this helps and shoot me an email/reply if you need furthur
assistance.

Good luck

 

[Quark]

It works great now. Thanks.

"Quark" <[email protected]> wrote in message

I have a list in 3 columns, SKU, Description, Price.
On another page will be an invoice where you select the item (SKU ), from a
drop down list, then all the other info relevant to it; the description and
price will fill in the next two columns. I can get the drop down selection
cell to populate with all the correct info, but I can't figure out how to
get the other cells to change when I select the SKU in the first cell. I
know I have to use the Index or Lookup functions, but I can't seem to quite
get it right. Any help would be appreciated.

Vlookup would probably be the easiest way to do this...

In BOOK3 on Sheet1 you have :
A B C
1 SKU Des Price
2
3 2522 1 1/4 in Screw $1.25
4 7777 3/4 Washer $3.25
5 9999 Hex Nut $5.55

On your INVOICE page you have :

A B C
1 INVOICE
2
3 SKU Description Price
4 2522 1 1/4 in Screw $1.25

You said you have the drop down box working in the SKU column so I'll
skip that.

Open both worksheets

Under description type "=VLOOKUP(A4," and then go to BOOK3 where you
have the SKU/Des/Price table and highlight the whole table. In this
case it would be A3:C5. Continue by typing ",2,false)"

That should pull the description. Put the same exact formula under
price, except change the ,2, to ,3, (you're telling the formula to
pull the 3rd column instead of the 2nd). And you should be all set.
End formula should be

=VLOOKUP(A4,[Book3]Sheet1!$A$3:$C$5,3,FALSE) for the price.

you will get a N/A if the formula can not pick up the sku and you can
avoid that by putting an IF statement in there like this

=IF(ISERROR(VLOOKUP(A4,[Book3]Sheet1!$A$3:$C$5,3,FALSE))=TRUE,0,VLOOKUP(A4,[
Book3]Sheet1!$A$3:$C$5,3,FALSE))

Hope this helps and shoot me an email/reply if you need furthur
assistance.

Good luck