Choose List, put the cursor in the Source field and type this formula: =INDIRECT(I3) J3, and choose Data Validation on the Data tab (or shortcut Alt→D→L). Step 4: Make the drop-down menu for the sub-categories Choose List, put the cursor in the Source field and press F3 to open the Paste Name window. I3, and choose Data Validation on the Data tab (or shortcut Alt→D→L). Let’s make the first drop-down, the one for the countries. Step 3: Make a drop-down menu for the main category
It is very important that the names of the sub-category ranges are the same as the names in the first column, otherwise the dynamic drop-down list won’t work. Select all the entries in a column and use the shortcut Ctrl + Shift + F3 to create a name for the range. Important: The heading of each sub-category must be exactly the same as the name in the main category.
It is absolutely critical that the data table is set up correctly: The main category (the countries in this example) should be in one column, and the sub-categories (the cities) in the other columns, with the countries repeated as headings. It’s easy, and it only takes five minutes! Step 1: Enter the data In this post I will show a very easy way to make a drop-down list in Excel that changes depending on what you have chosen in another cell. In a previous post we learned how to make an Excel drop-down menu in 3 minutes.