Creating a drop down list but show different values

Hello guys,


My English is not good, so I'm going to try simplify my problem in hope that you will understand me.

I have two sheets. On the Sheet 1 there is a cell that supposed to be a drop down list created via Data Validation where you choose full name of a nation and gets its three letter country code (e.g. choose United States, gets USA).

The country names and codes are in columns A and B on Sheet 2.

I've tried some guides on the internet, but failed miserably.

Do you, guys, have any idea how to do it?

PS: It needs to be in one cell, I know there is a easy way to get the code in other column, but that won't work for me.

I really appreciate your time.

Welcome to the forum!

I have not seen that. There are dynamic offset methods but those require 2 cells.

If I just did one cell, I would use a worksheet Selection event to open a filled userform's listbox control. That would show both columns. Pick a row and either column value can be returned.

Try the attached.
Setting validation in A1:A10 in Sheet1.

1) To ThisWorkbook code module

Please Login or Register  to view this content.

2) To Sheet1 code module

Please Login or Register  to view this content.

Thank you, it works!


Thanks guys for your help.

Have a good day.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.