linking the selection of a drop-down list to a quanity in another

[Guest]

I have a drop-down list of about 20 items and would like to then put a $
amount tied to what is selected from the drop-down list into another cell.
ex. if the dropdown list is purple, blue, black, yellow and white I would
like to easily formulate or "tie too" that color a dollar amount. If purple
is selected from the drop-down located in cell B3, then put $2.50 in cell C3
as a cost. I know that i can use an IF, THEN formula but the drop-down list
is very long and the formula would be very long. Is there an easier way to
do this?

 

[Guest]

You could use a select case construct within a worksheets Selection_Change
event handler.

You might want to record some macros choosing colors from the microsoft
palette to establish what the color index numbers are but for the illustation
below I have used the color name rather than index

Dim Color

Color = ActiveCell.Interior.ColorIndex

Select Case Color

Case Purple, Green, Blue

Activecell.Offset(1,0).Value = 2.50

Case Yellow

Activecell.Offset(1,0).Value = 5.00

Case Red

ActiveCell.Offset(1,0).Value = 10.00

Case Else

ActiveCell.Offset(1,0).Value = 20.00

End Select

Hope this helps

 

[rgew2004]

Why wouldn't you just have a list with the price for each value listed to
the right of each value, then use a vlookup to find the value selected in
the drop down?

Rgew2004