How to Make Dependent Drop Down List with Spaces in Excel

Creating a dependent drop down list is common practice among Excel users. However, generating a dependent drop down list with spaces in Excel requires the handling of spaces between words.

Let’s say we have 3 Product Lists with two-word names resulting in spaces among words. We have to generate a drop down list that allows spaces within dependent names. Excel INDEX, MATCH, and INDIRECT functions constitute formulas that allow spaces to exist in referenced names.

In this article, we demonstrate 2 easy ways to create Excel dependent drop down list with spaces.

How to Make Excel Dependent Drop Down List with Spaces: 2 Easy Ways

In Excel’s Data tab, Excel offers the Data Validation feature within the Data Tools section. Users easily make a drop-down list using the Data Validation feature. But while making a dependent drop-down list, spaces present within dependent entries cause errors in the process.

To overcome this, we use the following methods.

Method 1: Dependent Drop Down List with Spaces Using Combined Excel Functions

Combined INDEX and MATCH functions ignore spaces within dependent names or titles. The syntax of the INDEX function is

array; cell range or array.

row_num; the row position within the array.

col_num; the row position within the array. [optional]

area_num; used range in the array. [optional]

Again, the syntax of the MATCH function is

lookup_value; required match value in lookup_array.

lookup_array; an array or reference to match the lookup_value from.

match_type; by default, exact or next smallest=1, exact match=0, and exact or next largest=-1. [optional]

🔁 Drop Down List

Step 1: Place the cursor in any blank cell (i.e., F4) then Go to Data > Select Data Validation (from Data Tools section).

Step 2: The Data Validation window opens up. In the Data Validation Setting section,

Select Validation Criteria Allow as List.

Select B4:D4 as Source.

Click on OK.

➤ Now, in the worksheet, Click on the down arrow icon in cell F4. You see all the assigned cells as Source in the Data Validation window appears as drop-down list.

🔁 Dependent Drop Down List

Step 3: Repeat Step 1 to 2 for cell F5. Type the following formula as Source in the Data Validation window.

Comparing the formula to previously described syntaxes, the formula portion MATCH($F$4,$B$4:$D$4,0) passes the col_num to the INDEX function. B5:D13=array of the INDEX function. And it takes no row_num.

The MATCH portion takes F4 as lookup_value, B4:D4 as lookup_array and 0 refers to the exact match type.

Click on OK.

➤ Clicking on OK takes you back to the worksheet. In the worksheet, in cell F5 click on the down arrow icon and you see existing spaces don’t make an issue creating a dependent drop-down list.

Read More: Excel Dependent Drop Down List

Method 2: Using Define Name and INDIRECT Function

Earlier we demonstrated how the INDIRECT function is unable to create a drop-down list while spaces are present in the dependent names or references. To ensure the INDIRECT function allows spaces within dependent names we have to slightly modify the formula using the SUBSTITUTE function. The syntax of the INDIRECT function is

ref_text; reference in Text format.

a1; Boolean indication of A1. By default, TRUE = A1 style. [Optional]

🔁 Defining Names

Step 1: Select Column header (i.e., B4:D4) then Go to Formulas > Select Define Names (from Define Names section).

Step 2: The New Name window appears. In the New Name window,

Assign a Name for the cells (i.e., List).

Excel automatically takes cell reference for Refers to box.

Click on OK.

Step 3: Repeat Steps 1 and 2 of this method, to assign the name and range of different lists.

➤ You can check all the assigned names from Formulas > Name Manager (from the Define Name section).

🔁 Drop Down List

Step 4: Now, placing the cursor in the F4 cell go to Data > Select Data Validation (from the Data Tools section).

Step 5: The Data Validation dialog box appears. In the dialog box,

Under the Validation Criteria select List in Allow box.

Type =List (List is the defined name for column headers) in the Source dialog box.

Click on OK.

➤ If you click on the down arrow icons in cell F4, you see column headers as a drop down list as shown in the image below.

🔁 Dependent Drop Down List

Step 6: Execute Step 4 and 5 to display the Data Validation window, In the window,

Paste the following formula as Source.

 =INDIRECT(SUBSTITUTE($F$4,” “,”_”))

In the formula, the SUBSTITUTE function replaces spaces in the F4 reference with an underscore (_). You can use other characters to replace spaces in defined names and similarly in the INDIRECT formula. Therefore the INDIRECT function converts F4 entry similar to the assigned names. Depending on F4 values, the INDIRECT function displays the product list.

➤ After clicking on OK results in a dependent list to show up as depicted in the screenshot below.

You can change the dependent name entries from the first drop down list, as a result the entries in the second drop down list will automatically change.

Read More: How to Create Dynamic Dependent Drop Down List in Excel

Download Excel Workbook

Conclusion

In this article, we demonstrate formulas to insert Excel dependent drop down list with spaces. Combined INDEX and MATCH formula in-built takes spaces within dependent names or titles. However, we have to modify the INDIRECT function to allow spaces within dependent entries. Hope these above-mentioned methods excel in your requirements. Comment if you have further inquiries or have anything to add.

Related Articles

• Conditional Drop Down List in Excel
• How to Use IF Statement to Create Drop-Down List in Excel
• Create Excel Filter Using Drop-Down List Based on Cell Value
• Excel Formula Based on Drop-Down List
• How to Create Dependent Drop Down List with Multiple Words in Excel
• How to Extract Data Based on a Drop Down List Selection in Excel
• How to Populate List Based on Cell Value in Excel
• How to Change Drop Down List Based on Cell Value in Excel

<< Go Back to Excel Dependent Drop Down List | Excel Drop-Down List | Data Validation in Excel | Learn Excel