VBA drop down list help

[Mekinnik]

How can I create a VBA drop down list that will fire when the user selects a
certain cell from column e within a worksheet1. In addition I would like the
drop down to list text from another worksheet2 say column a, when the user
selects a name from this list it will populate the cell on worksheet1 with a
value/text from another column say column b from worksheet2.
I am trying to create a company list and to speed up the data entry and not
have to look up state abbreviations I would like to select the state name
from a drop down list and have it enter the abbreviation into the selected
cell.
I have tried to use the Data Validation tool, however it will only return
the name from the list. I am looking for a little bit more then it can offer.

 

[JNW]

I might be reading this wrong, but why won't data validation work? It seems
that what you want to do is fully supported by it.

 

[Mekinnik]

I want the user to select a name from the list, however I donnot want the
selected name to show in the active cell I want just two letters to show, and
I can not figure out how to make it do it. I have my Data validation list
referencing say sheet2 column a1-a50 that works fine, but when I select a
name from the list that name goes into the active cell and that is not what I
want. I want the corisponding 2 letter abbreviation to fill the active cell
based on the selection of the drop down list selection.

 

[JNW]

I would suggest doing the data validation drop down for the full name then in
the cell right next to it do a vlookup for the abbreviation. If you want to
consider a VBA solution that can be done (I think), but I would need to take
a look at it.