Hello, I was looking for a way to install a dropdown abbreviated states list that would also conicide with cities for the selected state, and zipcodes. Any help would be greatly appreciated. Thanks again!
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Excel Abbreviated States,Cities,Zipcodes
0
Answers
0
Selected Answer
The solution I have created for you is demonstrated in the attached workbook. It has two sheets. One has the lists. I have named it Lists. The other displays the selections. I called it Main.
The Lists sheet has many tables. There is one listing all the states. It's called States and has a column for the state names and another for abbreviations. The Data Validation drop-down at Main!B3 draws on this list. Observe that the Source for the validation is defined as
=INDIRECT("States[States]")As you add more names to the table they will be reflected in the drop-down as will be the case for modifications. The table controls everything.
Main!D3 changes as the state selection is changed. Observe the formula in that cell. The list for the drop-down in Main!B10 also changes with the state selection. That leads to an error in Main!D10 if the city shown in B10 isn't in the state newly selected in B3. A new selection must be made in B10 after B3 is changed. You might design something to hide the error.
Here is the formula that makes the data validation in B10 work.
=INDIRECT(SUBSTITUTE($B$3," ","_")&"[City]")As you see, it modifies the state name to convert it into a valid table name. The state name "New York" is turned into a table name "New_York". Note that the same substitution is also done in the formula in Main!D10. As a talking point, I included "Washington DC". "Washington_D.C." wouldn't be a valid table name because of the periods.
There should be a table in Lists for every state. Each table must be named following the convention for both the list of states and table names, as explained above. Each table must have a column called City. Note that the Zip column is referred to as column 2 by the VLOOKUP functions in Main!10:10. You can modify each table as required but as you add items you will probably find it more convenient to have all of them starting in column A. If you don't, make sure that you don't add blank rows into tables on the right as you add rows to tables on the left.
You can add more columns for additional information and access it using the same type of VLOOKUP already demonstrated. You can also easily sort the tables. That will change the sequence in the dropdown lists.