Create Drop-Down List Using INDIRECT Function in Excel

This article illustrates how to use the INDIRECT Function in excel to easily create a drop-down list. You will also learn how to create a dependent drop-down list in excel using this function. The following picture highlights the purpose of this article. Have a quick look through it to learn how to do that.

Table of Contents hide

Download Practice Workbook

Introduction to Excel INDIRECT Function

2 Methods to Create Drop-Down List Using INDIRECT Function in Excel

1. Create Drop Down List from an Excel Table Using INDIRECT Function

2. Create a Dependent Drop Down List with INDIRECT Function

Things to Remember

Conclusion

Download Practice Workbook

You can download the practice workbook from the download button below.

Dropdown List with INDIRECT Function.xlsx

Introduction to Excel INDIRECT Function

Objective:

You can use the INDIRECT function in excel to create a reference from a text string. References created in this way will not shift even if you insert rows or columns in your worksheet.

Syntax:

INDIRECT(ref_text,[a1])

Arguments:

ref_text : Required. Returns the reference specified by this text string.

[a1] : Optional. If True or omitted, ref_text is considered an A1-style reference. If False then the ref_text is interpreted as an R1C1 (row1 column1)-style reference.

2 Methods to Create Drop-Down List Using INDIRECT Function in Excel

Here I am going to illustrate the 2 ways you can create a drop-down list in excel with the INDIRECT function. So let’s begin.

1. Create Drop Down List from an Excel Table Using INDIRECT Function

Assume you have an excel table containing a list of States in the USA. Now you want to create a drop-down list in cell D5 showing the names from the table. Then follow the steps below to do that using the INDIRECT function.

📌 Steps

First, select cell D5. Then select Data >> Data Validation as shown in the following picture.

In the Settings tab, choose List as the Validation criteria using the dropdown arrow. Then enter the following formula in the Source field.

=INDIRECT("Table1[States]")

After that, go to the Error Alert tab as shown below. Here Table1 refers to the name of the table and States refers to its’ header.

Next, uncheck the Show error alert after invalid data is entered, and then hit the OK button.

Finally, you will see a drop-down list created as follows.

The dropdown list works for dynamic data too. Enter values in the cells below the table. Then the drop-down list will update too.

2. Create a Dependent Drop Down List with INDIRECT Function

Now imagine you have the following dataset instead. It contains the most populous cities of a few states. Now you want to select a state from a drop-down list in cell F5. Then you want to select a city in cell G5 from another dropdown list depending on the selected state. Then you need to follow the steps below.

📌 Steps

First, select the dataset with headings as shown below. Then press CTRL+SHIFT+F3 to create a named range for cities of each of the states. After that, check Top Row and then hit the OK button.

Now select cell F5. Then select Data >> Data Validation as earlier. Then choose List as the Validation criteria from the Settings tab. Next, enter the following formula for Source. After that, hit the OK button.

=$B$4:$D$4

Then you will see a drop-down list showing the names of the states as follows.

Now, select cell G5 and, then Data >> Data Validation. In the Settings tab, select List as the validation criteria. Then enter the following formula for Source. After that, hit the OK button.

=INDIRECT(SUBSTITUTE(F5," ","_"))

🔎 How Does the Formula Work?

➤ (SUBSTITUTE(F5, ” “, “_”))
Here, the SUBSTITUTE function replaces any spaces (” “) in the text in cell F5 with the underscore (_).

Output: “New_York”

➤ INDIRECT(SUBSTITUTE(F5, ” “, “_”))
Then the INDIRECT function creates a reference using the new text returned by the SUBSTITUTE function.

Output: {“New York City”;”Buffalo”;”Rochester”;”Yonkers”;”Syracuse”;”Albany”}

After that, select Yes if you see the following error alert.

Now select a state in cell F5 from the dropdown list. Then select cell G5. After that, click on the dropdown arrow at the lower-right corner of the cell. You will see the list of cities accordingly.

Then select another state from the drop-down list. After that, the dropdown list for cities in cell G5 will also change as shown below.

Things to Remember

The dynamic dropdown list works with an excel table only.
You can see the list of named ranges by clicking on the dropdown arrow in the Name Box. You can also find them in the Name Manager from the Formulas tab.

Conclusion

Now you know how to use the INDIRECT function in excel to create a drop-down list. Please let us know if this article has helped you to solve your problem. You can also use the comment section below for further queries or suggestions. Do visit our ExcelDemy blog to learn more about excel. Stay with us and keep learning.