Hello,

Is it possible to set up a drop-down for all 50 states? When the user opens the drop-down they can see (for example) New York-NY, California-CA, etc Then when a state is chosen, only the 2-character state is entered into the cell.



Your help is greatly appreciated.

Thank you,
Karen


Using Excel 365

By "drop-down" you are meaning "Data Validation List". When yes, then it isn't possible. Easiest workaround will be:
On separate sheet create a table, where 1st column lists all 50 States, and in second column are 2-character codes for those States. Define the 1st column of this table as named range. In your original table, set Data Validation List with defined Name as list source (using Names is only way to use tables in another sheets as Data Validation Source). In another column, use VLOOKUP formula to get 2-character codes from States table, depending on your selection you did using Data Validation.

Thank you so much for your help. I’ve been trying to figure this out but get stumped at VLOOKUP. I have used VLOOKUP before but cannot get this to work with a drop-down. Can you shed some more light on this? Your help would be greatly appreciated.

Thank you,
Karen

Another method ... (see attached)

Attached Files

States Selection and Abbreviation.xlsm (8.8 KB, 5 views)

You don't use VLOOKUP in Data Validation. In Excel, you need to use 2 columns for what you want to achieve. In first you select State code, and in second the formula retrieves according State name from States table. (In second column you probably want to check for State code not being an empty string too!)

Logit:
Excel has limitation for number of nested IF's (7 when I remember properly). So for 50 States this doesn't work. There is a workaround using additional IF's to group States to smaller subgroups (for every subgroup level the number of nested IF's you can use will be reduced by 1 - so when using 2 subgroups, you can nest 2*6=12 IF's, when using 2*2 subgroups, then you can nest 4*5=20 IF's, etc.), but this formula will be very long and clumsy, and 50 will be anyway out of reach.
The another workaround is use formula like
No limitations there except the fomula length, but ...

Thank you so much! I'll check it out and see if I can get it to work for me.