Help with drop down menu

[wilecoyote]

i am trying to create a worksheet where i am picking items out of
inventory

cell "A-1" has drop down menu which i select item (no problem, i can do
that)

Cell "B-1" is the corresponding price for the item selected in cell "A-1"

example: the drop down allows me to select widgets, modifed widgets,
budget widgets, or premium widgets. (up to about 30 choices)......the
price of widgets is 10.00, the price of modified widgets is 20.00 the
price of budget widgets is 5.00 while the price of premium widgets is
30.00 and so on.......

how do i get cell "B-1" to display the correct price according to the
selection from the drop down in cell "A-1"

thanks in advance for any help you may be able to provide

WC

 

[JulieD]

Hi

unless you need a code (vba) solution, you can achieve what you want using
the VLOOKUP function in the worksheet itself.

if you have a table somewhere (say Sheet2) of widget types in column A and
costs in column B then the VLOOKUP formula would be

=VLOOKUP(A1,Sheet2!$A$1:$B$35,2,0)
this says lookup the value of A1 in sheet 2 column A and return the
associated value from column B where there is an exact match.

you may want to embed this in an IF function to deal with the situation
where the selection in A1 is not found or is blank
=IF(ISNA(VLOOKUP(A1,Sheet2!$A$1:$B$35,2,0)),"",VLOOKUP(A1,Sheet2!$A$1:$B$35,2,0))

Cheers
JulieD

 

[Tom Ogilvy]

Look in Excel Help at the Vlookup worksheet function.

You will need to create a table with Item names on the left and price next
to it in the next column.