Pick lists that shows one text string but picks different text...

[bbbb1234]

I have a list of state names on Col A and the two letter state abbreviation in Col B. I want a drop down menu in cells, say C2 through C10, that shows the full state name when the drop down is selected, but when a state is selected, only the two letter abbreviation is entered in the cell. I thought about using Data Validation but could not figure out how to make this work.

I prefer to not use VBA.

Thoughts on how to do this?

 

[PeteWright]

Hi @bbbb1234

I'm afraid that you can't get the desired results without VBA, because Data Validation only shows what's in a list, but not a different value.
However you could add another cell or column to show your results.

Book1.xlsm
	A	B
1	state name	abbrev.
2
3	Arizona	AZ
4	California	CA
5	Florida	FL
6	Louisiana	LA
7	Nebraska	NE
8	New York	NY
9	Ohio	OH
10	Texas	TX
11	Virginia	VA
12	Wyoming	WY
13
14
Sheet31

Cell Formulas
Range	Formula
B14	B14	= IF( A14<>"",XLOOKUP(A14,A3:A12,B3:B12),"")

Cells with Data Validation
Cell	Allow	Criteria
A14	List	=$A$2:$A$12

Here is a screen capture:
Excel Drop Down (GIF)

 

[bbbb1234]

Hi @bbbb1234

I'm afraid that you can't get the desired results without VBA, because Data Validation only shows what's in a list, but not a different value.
However you could add another cell or column to show your results.

Book1.xlsm
	A	B
1	state name	abbrev.
2
3	Arizona	AZ
4	California	CA
5	Florida	FL
6	Louisiana	LA
7	Nebraska	NE
8	New York	NY
9	Ohio	OH
10	Texas	TX
11	Virginia	VA
12	Wyoming	WY
13
14
Sheet31

Cell Formulas
Range	Formula
B14	B14	= IF( A14<>"",XLOOKUP(A14,A3:A12,B3:B12),"")

Cells with Data Validation
Cell	Allow	Criteria
A14	List	=$A$2:$A$12

Here is a screen capture:
Excel Drop Down (GIF)

Hi PeteWright -
Thanks for providing the very creative solution!! I was hoping there was a way to do this without VBA but figured there was not. Unfortunately, I cannot figure out how to make your creative solution work in my current application. Could you provide the VBA code that you would recommend?
Many thanks!!!

 

[PeteWright]

Could you provide the VBA code that you would recommend?

I have no code of my own, but found this: How to create drop down list but show different values in Excel?

 

[bbbb1234]

I have no code of my own, but found this: How to create drop down list but show different values in Excel?

AWESOME! I will study it to see how it works to implement!!! Thanks for your help!!!

 

[bbbb1234]

AWESOME! I will study it to see how it works to implement!!! Thanks for your help!!!

That worked!!! Thanks so much for posting it here!!! The Mr. Excel Community ROCKS!!!!!

 

[PeteWright]

@bbbb1234 glad to hear I could help, even if it was not my own work