Best Way To Substitute Text From Drop Down List?

**tradersteve** · 10-16-2010, 04:54 PM

I have a list of approximately 100 products that I would like to give users a dropdown list to select from BUT while I want the list to show the product name I need the value placed into the cell to be its 5 to 7 character product code.

Seems like something that should be possible but would really appreciate some direction in how to go about it.

Thanks,

**Palmetto** · 10-16-2010, 05:02 PM

Possible you could adapt this excellent look up approach from Jerry Beaucaire

Autocomplete Data Validation

Download the sample file and give it a test drive.

**tradersteve** · 10-16-2010, 11:11 PM

Thanks for the referral to the data validation samples. I'm afraid the best adaption I could come up with won't be nearly as elegant (or compact) as Mr. Beaucaire's sample but it will work if I make a really LONG drop down list and only go one level deep..

Perfect would be if I could extend the validation out one more layer but I couldn't figure out how to write it.
This code

Please Login or Register  to view this content.

in data validation drills down from <Blank> to <D-names> to <individually listed names>. If its possible I would like help going one more list deep where the individual name would have a 1 item list under it containing the abbreviation code for that proper name. (i.e. Daniel = DG AB)

**DonkeyOte** · 10-17-2010, 03:52 AM

If VBA is viable you can use a Change Event on the DV cells to perform the lookup upon alteration and override the cell result
(edit: this approach takes advantage of one of DV's flaws - a pasted value is not "validated")

Please Login or Register  to view this content.

the above is a very simplistic example but basically states that DV cells are D1:D10 and that DV source lookup table is A1:B10 where A1:A10 hold the values sourcing the DV list itself and B1:B10 represent the codes to be returned to the cell upon selection.

**tradersteve** · 10-19-2010, 01:14 AM

Thanks to you both for the guidance. Both methods get me where I originally hoped to be but since the product list changes frequently it will probably be better to go the vba route for ease of changing the items in my list.

THanks again!