Create a Drop-down List Using the INDIRECT Function in Excel - 2 Methods

This is an overview.

The Excel INDIRECT Function

Objective:

Create a reference from a text string. References created will not change if you insert rows or columns.

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, the ref_text is interpreted as an R1C1 (row1 column1)-style reference.

Method 1 – Create a drop-down List from an Excel Table Using the INDIRECT Function

The excel table contains a list of States in the USA.

Steps

Select D5.
Select Data >> Data Validation.

In the Settings tab, choose List in Allow.
Enter the following formula in Source:.

=INDIRECT("Table1[States]")

Here Table1 is the name of the table and States refers to its header.

Go to the Error Alert tab.

Uncheck Show error alert after invalid data is entered.
Click OK.

The drop-down list is created:

It is dynamic:

Enter values in the cells below the table.

The drop-down list will update.

Method 2 – Create a Dependent drop-down List using the INDIRECT Function

Consider the following dataset. To select a state from the drop-down list in F5:

Steps

Select the dataset with the headings.
Press CTRL+SHIFT+F3 to create a named range for the cities of each of the states.
Check Top Row.
Click OK.

Select F5.
Select Data >> Data Validation.
In the Settings tab, choose List in Allow.
Enter the following formula in Source:.
Click OK.

=$B$4:$D$4

A drop-down list shows the names of the states.

Select G5.
Select Data >> Data Validation.
In the Settings tab, choose List in Allow.
Enter the following formula in Source:.
Click OK.

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

Formula Breakdown

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

Output: “New_York”

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

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

Select Yes if you see the following error alert.

Select a state in the drop-down list.
Select G5.
Click the drop-down arrow at the lower-right corner of the cell. You will see the list of cities.

Select another state in the drop-down list. The cities in G5 will also change:

Things to Remember

The dynamic drop-down list works with an Excel table only.
You can see the list of named ranges by clicking the drop-down arrow in the Name Box. You can also find them in Name Manager (Formulas tab).

Create a Drop-down List Using the INDIRECT Function in Excel – 2 Methods

The Excel INDIRECT Function

Method 1 – Create a drop-down List from an Excel Table Using the INDIRECT Function

Method 2 – Create a Dependent drop-down List using the INDIRECT Function

Things to Remember

Related Articles

Leave a reply Cancel reply