Excel Dependent Drop Down List

Excel Dependent Drop Down List

https://softtechtutorials.com/microsoft-office/excel/excel-dependent-drop-down-list/

0:00 Intro Dependent Drop Down List 0:30 Named Ranges 0:50 Name Manager 1:20 Name Box 2:18 Inserting Named Ranges using Name Manager 3:22 INDIRECT() function 3:41 Dependent Drop Down List

In this tutorial I will explain how to create a drop down list that is dependent on the answer to another drop down list. We will do this without using any complex formulas.

Named Ranges The idea is to name the different ranges and refer to the ranges in the Data Validation menu with their corresponding names. The ranges’ names can be managed by navigating to the Formulas and selecting Nama Manager in the Defined Names section.

Inserting Named Ranges using Name Manager The first option, is to click on New in this menu and define the name of the first range.

Inserting Named Ranges using Name Box Now, we show you a second and faster method to define a named range. We close this menu and select the next range under Europe that we want to give a name. To name the range it suffices to type the name you want to give the range in the Name box on the screen.

Creating Named Ranges from Selection for dependent drop down list. Next, we show how we can implement all names at once when the data is set up as it is here.

We select all data, navigate to Formulas and the Defined Names section again. Here, we go for Create from Selection now. We click on it see a menu opening. You can create names from values in the selected data now.

We quickly change this in our Workbook and move on to the implementation of the drop down menus.

INDIRECT() function Before we can implement the dependent drop down menus, we first show how exactly the INDIRECT function works as we will use this in the Data Validation menu. We click on a cell in the sheet and type equals INDIRECT of Continent for example.

Dependent Drop Down List The Data Validation menu opens where we can insert our drop down lists. We set Any value to List and type equals Continent in the Source box.

Note that there could be a mismatch between the item in the continent list and the names of our ranges because we have inserted an underscore instead of spaces. To take this into account we use the substitute function to replace the underscore by a space.

This concludes our tutorial on Excel Dependent Drop Down List. If you have any questions or comments let me know. I'm inspired by content creators as Leila Gharani and Teacher's Tech.

#Excel #Tutorials #HowTo ... https://www.youtube.com/watch?v=mPpEIdK5y_8