Returning different value in Dropdown List

Hello - I wonder if someone would please help me or point me to the right place with the below.

I want to create an Excel Drop-Down menu with the below condition:
- I have 4 items in columns A and B, respectively, as listed below:
BVLV | Ball Valve
BTLV | Butterfly Valve
GVLV | Gate Valve
GBLV | Globe Valve
- The drop-down menu displays column B, however, upon selecting any item from this column, only the four-letter "valve code" from column A is returned.

Alternatively, I can make a column C that displays both A and B together (merged, separated by a 'hyphen', and then make a drop-down list of this column C, but still wanted column A returned.

The above is a simplified example from a list of hundreds of items. The reason I only needed the 4-letter code to be displayed because it will be exported to another form later on, and I didn't want the user to constantly refer back at the "source" data. I am not sure if exporting is possible if I select a 'combo-box' (either from Control or ActiveX), so I would prefer the normal Data Validation List method, since that has the "in-cell dropdown" option.

Any help as to how to go about this would be much appreciated.

Thanks very much.

Hi again.
it's hard to estimate your needs without a good example of the workbook..
I do understand your questions, however there are many answers that might or might not suit your needs.

Also you say you want to learn this *thumbsup*

therefore:
Look at the comments in the attached file.
It's the same example as before, and I haven't changed the code itself, but explained what every lil'bit does. I've also added an example of what you can do.


suggestion:
in the "worksheet_change" routine, insert a breakpoint. (by pressing F9)
the code will then break as soon as it runs. you can then itterate through each line of code by pressing F8.
you will then see what each bit does, you can mouse-over the variables to see their values
and then get a better understanding of how it all works.


Hope this helps

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of their post to show your appreciation.

Hi.

to answer your question:
the code in a worksheet_change will only execute on that specific worksheet.
however, the code can get data from other worksheets
question 2: macros can be run from anywhere. but if you are to get data from ranges from another sheet, you should select that sheet first, then go back
see the following code for an example of what I mean (you can replace the code in my old example with this one)

Please Login or Register  to view this content.