What is Dependable Drop-Down Function in Excel?

Dependable drop-down function is an advanced function of data validation that allows us to create simple drop-down lists. With the help of dependable drop-down function, we can create drop-down lists for more than one cell, in such a way that items in dependent list change as we change the item in the drop-down list. This is useful when working with database with lots of categories & subcategories and makes data entry easier by just picking up the item from parent drop-down list and then dependent drop-down list.

Where to find Dependable Drop-Down Function in Excel?

Go to:

Data>Data tools>Data Validation>Settings

How to use Dependable Drop-Down Function?

Let us take an example to understand Dependable Drop-Down function. Suppose Zee Limited has different categories & subcategories of expenses. It wants to create a drop-down list for its main category of expenses and later on, on the basis of the main category of expenses, a drop-down list of sub category of expenses so that data can be entered easily, efficiently and without any error. We can use drop-down function to arrange data in the way Zee Limited wanted.

Step 1: Select cell where you want the parent drop-down list. Open Data validation and following dialog box will open:

Allow: refers to different sources for creating the drop-down list.

Data: allows different criteria for drop-down list depending upon your selection in Allow field.

Source: allows you to select the cell range to be included in drop-down list.

Step 2: In Allow field select List, in Source field specify the cell range that contains the main expenses. 

Click Ok and parent drop-down list will be created.

Step 3: Create name range for all the main expenses. (Formulas>Name Manager) or using short key ctrl+f3. After creating name range for all the main expenses:

Step 4: Select the cell where you want to create dependable drop-down list. Open Data Validation and repeat the steps as explained above, except now in Source field type =INDIRECT(D2). INDIRECT function of Excel gives name range of main expenses and their sub expenses a valid reference and enable us to use this reference in creating dependable drop-down list. Please note that D2 is the cell location of parent drop-down list. The window will look like:

Click Ok and check the outcome:

Now you have to select the main expense from parent drop-down list and dependable drop-down list will be automatically update.

You may notice that by using this function many data with different categories can be entered very efficiently and with any error.