I have set up drop down menus and are working perfectly, not too complicated i know. but I want to put pricing values behind the drop down menu text.
for example, I have a car production pricing spread sheet. I have the available colours in the drop down menu. how can i put different prices behind each colour, when each colour will cost differently.
Any help is welcome,
Many thanks, Keith
Here's an example where the drop down list of colors is in cell A1 (not shown below). The drop down is fed from the color list in column F which has associated prices in column G. Cell D1 is linked to the drop down and cell D3 has a formula which returns the price associated with whatever color is selected in the drop down list.
Sheet3
<table style="font-family: Calibri,Arial; font-size: 11pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 66px;"><col style="width: 60px;"><col style="width: 93px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td>
</td><td>
</td><td><-- Colors</td><td style="text-align: right;">2</td><td><-- LinkedCell</td><td style="text-decoration: underline;">ColorList</td><td style="text-decoration: underline; text-align: right;">Price</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>Red</td><td style="text-align: right;">$10</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td>
</td><td>
</td><td>Result --></td><td style="background-color: rgb(255, 255, 0); font-weight: bold; text-align: right;">$12</td><td>
</td><td>Blue</td><td style="text-align: right;">$12</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>Green</td><td style="text-align: right;">$11</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>Black</td><td style="text-align: right;">$14</td></tr></tbody></table>
<table style="font-family: Arial; font-size: 10pt; border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tbody><tr><td>
Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>D3</td><td>=CHOOSE($D$1,10,12,11,14)</td></tr></tbody></table></td></tr></tbody></table> Another approach if you have a long list of colors and colors and/or prices are changing frequently is to number the colors in column E (in this case from 1 - 4) and use this formula in cell D3:
Code:
=VLOOKUP($D$1,$E$2:$G$5,3)
Excel tables to the web >> Excel Jeanie HTML 4 
