| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
linking the selection of a drop-down list to a quanity in another
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
linking the selection of a drop-down list to a quanity in another
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 -- BerbatovIsGod "LJ" wrote: 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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
linking the selection of a drop-down list to a quanity in another
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 "PaxtonRoadEnd" wrote in message ... 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 -- BerbatovIsGod "LJ" wrote: 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Limiting selection in a cell AND linking that selection to a list | Excel Discussion (Misc queries) | |||
| Help with drop down list selection | Excel Programming | |||
| Linking a selection in a drop down list to a calc in another cell | Excel Worksheet Functions | |||
| Create a drop down list dependent on selection from another list | Excel Programming | |||
| Help with drop down selection list box | Excel Programming | |||
Linear Mode
