choose a product from a drop down list i need it to insert a price

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i need to know how to have a related price show up in column D when i choose
a product from a drop down list in column B

ex. B C D
TTW036 $6495.50


I have about 30 differant products each with their own price.
 
The typical way would be to have a 2-column list of Prod, Price (usually on a
separate sheet in the workbook)

If that list was on Sheet2 in cells A2:B31

Then....On sheet1, with cell A1 containing the drop-down value
B1:
=IF(ISNA(VLOOKUP(A1,Sheet2!$A$2:$B$31,2,0)),"",VLOOKUP(A1,Sheet2!$A$2:$B$31,2,0))
or
B1: =IF(A1="","",VLOOKUP(A1,Sheet2!$A$2:$B$31,2,0))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP
 
use the vlookup function in Col D
--
HTH,
Gary Brown
(e-mail address removed)
If this post was helpful to you, please select ''YES'' at the bottom of the
post.
 
Barnzy,

Use the VLOOKUP function tied to your table, along the lines of:

=VLOOKUP(B2,Table,2,False)

For example:

=VLOOKUP(B2,PriceSheet!$A$1:$B$30,2,False)


HTH,
Bernie
MS Excel MVP
 
That all looks great but i have no idea how to use the code. I have figured
out how to create a drop down list that refers to sheet 2 but can not figure
the rest out. Please bear with me as i have only just started using excel.
I will try to explain it.
On sheet2 I have 2 columns. In column A Rows 1-24 is a list of products
(that i have named "units") for a data validation list. In column B Rows 1-24
is the corresponding price of the units. such as B1 is the price of A1 and so
on.

Now on Sheet1 A12 is a drop down list (=units) Now i need Sheet1 D12 to show
a related price to the product chosen in A12.

Can someone hold my hand through this and help me wrap my head around this
so I can understand it.

Thanks
 
Hi, Barnzy

Let's create one more range name.....
On Sheet2
<insert><name><define>
Names in worktook: LU_Price
Refers to: A1:B24

Now...on Sheet1
D12: =IF(COUNTIF(Units,A12),VLOOKUP(A12,LU_Price,2,0),"")

Note: if the list of products and prices will continue to grow, you may want
to consider using Dynamic Range Names, which expand and contract to match the
volumn of data cells. Once again, Debra Dalgleish has the topic covered at
her website:
http://www.contextures.com/xlNames01.html#Dynamic

Does that help?

(Post back with more questions)
***********
Regards,
Ron

XL2002, WinXP
 
Thanks that worked great.

Ron Coderre said:
Hi, Barnzy

Let's create one more range name.....
On Sheet2
<insert><name><define>
Names in worktook: LU_Price
Refers to: A1:B24

Now...on Sheet1
D12: =IF(COUNTIF(Units,A12),VLOOKUP(A12,LU_Price,2,0),"")

Note: if the list of products and prices will continue to grow, you may want
to consider using Dynamic Range Names, which expand and contract to match the
volumn of data cells. Once again, Debra Dalgleish has the topic covered at
her website:
http://www.contextures.com/xlNames01.html#Dynamic

Does that help?

(Post back with more questions)
***********
Regards,
Ron

XL2002, WinXP
Click to expand...
 
Back
Top