Excel Drop Down List Question

[rideklein]

I can do the “typical” drop down list, where you create a source data list, do the data verification thing, and make the drop down.

What I need is a drop down list that selects a numeric value, but that is associated to a description.

Say my list is this:
101 Red
102 Orange
103 Yellow
104 Blue

So, when you click the drop down arrow, all the above is presented to select (numbers + associated descriptions). But... After you make your selection all that ends up in the cell is the numeric value (such as 103).

Help! Thank you, in advance.

 

[Gruuuu]

I don't believe there's a way to do exactly what you're asking, but it's possible you can still do what you're trying to do.
You can always create another cell that pulls out the numeric data after it's been selected from the list.

Say you have your Validation in Cell D1, and you have your 'pull-out' in cell E1.

[tt]
E1:
=VALUE(LEFT(D1,LEN(D1)-SEARCH(" ",D1)-1))
[/tt]
So this looks at D1, finds out where the space is, pulls out everything to the LEFT of the space, and converts it to a numeric value (because it still thinks it's all text)

Make sense?