How to Create an excel dropdown list that displays text with a numeric hidden value

Question

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

User · Answer

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.

User · Answer

Data validation drop down  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    Create your list with matching values   On the Data tab choose Data Validation  The Data validation form will be displayed Set the Allow dropdown to List  Set the Source range to the first part of your list Click on OK  User messages can be added if required    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     Form control drop down  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    Create your list of data in a worksheet Click on the Developer tab and dropdown on the Insert option In the Form section choose Combo box or List box Use the mouse to draw the box on the worksheet Right click on the box and select Format control The Format control form will be displayed Click on the Control tab Set the Input range to your list of data Set the Cell link range to the cell where you want the number of the selected item to appear Click on OK

[excel] How to Create an excel dropdown list that displays text with a numeric hidden value

Examples related to excel