#1
|
|||
|
|||
Drop-down for States
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 |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
Another method ... (see attached)
Code:
=IF(A1="Alabama - AL","AL",IF(A1="Florida - FL","FL",IF(A1="Washington - WA","WA",""))) |
#5
|
|||
|
|||
Quote:
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 Code:
=IF(StateCode=CodeValue1,TextValue1, "") & IF(StateCode = CodeValue2, TextValue2, "") & ... |
#6
|
|||
|
|||
Thank you so much! I'll check it out and see if I can get it to work for me.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Extract States from a String | soldat452002 | Excel | 9 | 04-26-2018 04:16 AM |
Drop down box list based on response to another drop down box | Phideaux | Excel | 16 | 04-13-2018 03:07 AM |
Drop down lists and pulling data from worksheet based on drop down selection | cjoyce73 | Excel | 5 | 07-17-2017 07:40 AM |
How to import a map from mappoint 13 into PP 13 and then color fill individual states | rideninc | PowerPoint | 0 | 01-22-2014 03:50 PM |
Analysis of states and income levels | knownunknown | Excel | 1 | 01-22-2011 06:35 PM |