See how to create cascading drop-down lists with multi-word entries. The items in your primary drop-down list must be one-word entries.However, this method has a number of limitations. This is the easiest way to create cascading drop-down boxes in Excel. Where A2 and B2 contain the first and second dropdowns, respectively.Īs the result, your 3 rd drop-down list will display the regions corresponding to the Fruit and Country selected in the first 2 drop-down lists. For example, in cell C2, the data validation formula would be: Apply Excel Data Validation with the INDIRECT SUBSTITUTE formula that concatenates the names of the entries in the first two columns, and removes the spaces from the names.These names should not contain underscores or any other additional characters. Then you create named ranges MangoIndia, MangoBrazil, OrangesIndia, OrangesBrazil, etc. For example, you have Mango, Oranges, etc. Create additional sets of named ranges, and name them for the word combinations in your first two dropdowns.If you need to create a cascading drop down menu that depends on the selections both in the first and second drop-down lists, then proceed in this way: Create a third dropdown dependent on the first two lists The displayed list of regions is unique for each country but it does not depend on the selection in the first drop-down list. Now, each time you select India under the list of countries in column B, you will have the following choices in the third drop-down: For instance, a list of Indian regions should be named "India", a list of Chines regions - "China", and so on.Īfter that, you select a cell for the 3 rd dropdown (C2 in our case) and apply Excel Data Validation with the following formula (B2 is the cell with the second drop-down menu that contains a list of countries): Just remember the 2 important things discussed above, which are essential for the correct work of your cascading drop-down lists.įor instance, if you want to display a list of regions in column C depending on which country is selected in column B, you create a list of regions for each country and name it after the country's name, exactly as the country appears in second dropdown lists. You can make the drop-down list of this type in the same fashion as we've just made a second dependent drop-down menu. Set up 3rd dropdown that depends on 2nd list If needed, you could add a 3 rd cascading drop-down list that depends either on the selection in the 2 nd drop-down menu or on the selections in the first two dropdowns. Add a third dependent drop-down list (optional) Safely click Yes, and as soon as you select an item from the first drop-down menu, you will see the entries corresponding to it in the second, dependent, drop-down list.ĥ. If cell A2 is currently empty, you will get the error message " The Source currently evaluates to an error. Where A2 is the cell with your first (primary) drop-down list. But this time, instead of the range's name, you enter the following formula in the Source field: Select a cell(s) for your dependent drop-down menu and apply Excel Data Validation again as described in the previous step. Go to the Data tab, click Data Validation and set up a drop-down list based on a named range in the usual way by selecting List under Allow and entering the range name in the Source box.įor the detailed steps, please see Making a drop down list based on a named range.Īs the result, you will have a drop-down menu in your worksheet similar to this:.In the same or in another spreadsheet, select a cell or several cells in which you want your primary drop-down list to appear.When done, you may want to press Ctrl+F3 to open the Name Manager window and check if all of the lists have correct names and references. For example, the dependent list to be displayed when " Mango" is selected from the first drop-down list should be named Mango. The names of the dependent lists must be exactly the same as the matching entry in main list.If you have items consisting of two, three or more words, please see How to create a cascading dropdown with multi-word entries. The items to appear in the first drop-down list must be one-word entries, e.g.Please pay attention that if your first row is sort of column header like you see in the screenshot above, you shall not include it in the named range.įor the detailed step-by-step instructions please see How to define a name in Excel.
0 Comments
Leave a Reply. |