Labels for items in drop down list

Hi there,

I want a drop down list using the data validation tool tied to a name range in another sheet.

I can do this simple task with no problems.

However i want the user to click the list and see the value (which is an three letter abbreviation), plus the full title. However, on selecting the appropriate value from the list, i want only the abbreviated value to appear in the cell.

eg.
1. user clicks arrow for list;
2. options displayed are: "PRO - Project", "SOR - Schedule of rates", "VAL - Validation"....etc.;
3. user selects PRO - Project (for example)
4. cell displays "PRO"

I understand that this may not be possible using the data validation method, since the list only displays the data defined in the name range, but if there is an alternate method which can be used to achieve the above, I would love to know what it is.

I'm using Excel 2007, by the way

Any help gratefully accepted

This does require VBA to accomplish, and it's not an uncommon request.

Data Validation - Make Selection From Detailed Items but see Abbreviated Entry Afterwards
This is a VBA solution that watches for you to make a selection from a drop down data validation list, then replaces that choice with an abbreviated text string found adjacent to your original DV selection's source list. You would set up a table with your source list in the first column and your replacement strings in the second column, then a Worksheet_Change event macro is installed into the sheet where you are using the DV list. It makes the replacements for you in realtime.

Replace Current DV Choice

There is a sample sheet where you can test this technique out and see it in action.