Data validation dropdown list

[gta1216]

I have a dropdown list created from data validation. I'm trying to create a dropdown list that shows the abbreviation and on the right it shows the name of the US state. When the state is selected, only the abbreviation will be displayed in the cell. I do not want to use macro. Is this possible?

I currently have over 300 rows and over 30 columns of data that I want to convert.

 

[6StringJazzer]

It is not possible to do exactly what you have described without a macro. The data validation dropdown list allows you to select a value for the cell from a list. The value you select becomes the value in the cell. You cannot select one value, but have the cell take on a different value. (Further, using data validation with a list means that the cell is restricted to values that are in the list, so I'm not sure you could do this even with a macro.)

An alternative would be to select the item from the list and then show only the abbreviation in another cell.

If you explain why you want to do it this way we might be able to offer additional options.

 

[gta1216]

Data validation is not mandatory. I am flexible with how to make this happen.

Is it possible to pick from a list of complete names, but only show the abbreviation? I prefer to avoid macro.

 

[Fluff]

Maybe something like this

+Fluff New.xlsm
	A	B	C	D	E
1	State	Abbreviation
2	Alabama	AL		Alaska	AK
3	Alaska	AK
4	Arizona	AZ
5	Arkansas	AR
6	California	CA
7	Colorado	CO
8	Connecticut	CT
9	Delaware	DE
10	Florida	FL
11	Georgia	GA
12	Hawaii	HI
13	Idaho	ID
14
Cases

Cell Formulas
Range	Formula
E2	E2	=VLOOKUP(D2,A2:B13,2,0)

Cells with Data Validation
Cell	Allow	Criteria
D2	List	=$A$2:$A$13

 

[shaowu459]

Something like this?

 

[6StringJazzer]

Something like this?

It might be useful if you were to explain how this is implemented.

 

[shaowu459]

D1=USA(Alt+Enter)The United States of America
D2=UK(Alt+Enter)United Kingdom of Great Britain
you can create above text by formula.

The drop down list in B1 refer to D1:D2, and set the first row height just one row height, wrap text for B1, vertical alignment set to at the top.

 

[6StringJazzer]

The drop down list in B1 refer to D1:D2, and set the first row height just one row height, wrap text for B1, vertical alignment set to at the top.

I see. So the full content is in the cell, but just not visible. Thanks.

 

[shaowu459]

I see. So the full content is in the cell, but just not visible. Thanks.

Yes, the full contents still in the cell. You may cannot select or enter a string in one cell and the string will automatically change to another string without Macros or auto correction.

 

[gta1216]

D1=USA(Alt+Enter)The United States of America
D2=UK(Alt+Enter)United Kingdom of Great Britain
you can create above text by formula.

The drop down list in B1 refer to D1:D2, and set the first row height just one row height, wrap text for B1, vertical alignment set to at the top.

I think this should work. Can

Maybe something like this

+Fluff New.xlsm
	A	B	C	D	E
1	State	Abbreviation
2	Alabama	AL		Alaska	AK
3	Alaska	AK
4	Arizona	AZ
5	Arkansas	AR
6	California	CA
7	Colorado	CO
8	Connecticut	CT
9	Delaware	DE
10	Florida	FL
11	Georgia	GA
12	Hawaii	HI
13	Idaho	ID
14
Cases

Cell Formulas
Range	Formula
E2	E2	=VLOOKUP(D2,A2:B13,2,0)

Cells with Data Validation
Cell	Allow	Criteria
D2	List	=$A$2:$A$13

I don't understand how this works.