![]() 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |