List Only

How can I have a dropdown list in Excel automatically select the source based on a preselected category?

For example, a user first picks a category from a drop down list. Based on the category that is picked, I need a second dropdown list to only show sub categories relevant to the first category picked. So lets say the first category is US states. The second list will then populate with the 3 largest cities in whatever state was picked Thanks!

Public Comments

1. For your example, you would have to set 50 Named Ranges, each named for a state and containing the 3 largest cities in that state. To do that, select a column somewhere out of the way and enter the 3 largest cities for, say, California. Highlight the 3 cities and go to Insert > Name > Define. In the text box at the top enter 'California' (no quotes) and click 'Add'. Click 'OK' again. Repeat this for all 50 states. Then set a Data Validation List for the first drop down containing the names of the 50 States. Assume that drop down is in cell A1 Lastly, create a second Data Validation drop down menu. Select 'List' as the 'Allow', and in the Source text box enter: =Indirect(A1) Now, when you select a state in the first drop down, the 3 largest cities for that state will be displayed in the second drop down.