I am trying to create a drop down list that displays text with a hidden numerical value attached. Then I will have a standard formula on the same row that calculates a value based upon the hidden value selected.
This question is related to
excel
There is a list option in Data validation. If this is combined with a VLOOKUP formula you would be able to convert the selected value into a number.
The steps in Excel 2010 are:
In a cell enter a formula like this
=VLOOKUP(A2,$D$3:$E$5,2,FALSE)
which will return the matching value from the second part of your list.
Alternatively, Form controls can be placed on a worksheet. They can be linked to a range and return the position number of the selected value to a specific cell.
The steps in Excel 2010 are:
There are two types of drop down lists available (I am not sure since which version).
ActiveX Drop Down
You can set the column widths, so your hidden column can be set to 0.
Form Drop Down
You could set the drop down range to a hidden sheet and reference the cell adjacent to the selected item. This would also work with the ActiveX type control.
Source: Stackoverflow.com