How to Make Dependent Drop Down List with Spaces in Excel

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.

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

While making a dependent drop-down list via the Data Validation feature, spaces within dependent entries cause errors. Luckily, there are a few workarounds.

Method 1 – A Dependent Drop-Down List with Spaces Using Combined Excel Functions

Combined INDEX and MATCH functions can ignore spaces within dependent names or titles

Drop Down List

Steps:

• Place the cursor in any blank cell (i.e., F4).
• Go to Data.
• Select Data Validation (from Data Tools section).

• The Data Validation window opens up.
• Select Validation Criteria Allow as List.
• Select B4:D4 as Source.
• Click on OK.

• In the worksheet, click on the down arrow icon in cell F4. You’ll see all the assigned cells as Source in the Data Validation window as a drop-down list.

Dependent Drop Down List

• Create a drop-down list in cell F5. Use 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.

• In cell F5, click on the arrow icon and you’ll get a full list of the results.

Read More: Excel Dependent Drop Down List

Method 2 – Using Defined Names and INDIRECT Function

Defining Names

• Select the column headers (i.e., B4:D4).
• Go to Formulas.
• Select Define Name (from the Defined Names section).

• The New Name window appears.
• Assign a Name for the cells (i.e., List).
• Excel automatically puts a cell reference for Refers to box.
• Click on OK.

• Assign names to all three product lists.

• You can check all the assigned names by going to Formulas and selecting Name Manager (from the Define Name section).

Drop Down List

• Select the F4 cell.
• Go to Data and select Data Validation (from the Data Tools section).

• The Data Validation dialog box appears.
• 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 open the menu on cell F4, you’ll see column headers as options.

Dependent Drop Down List

• Create another Data Validation window below the first one.
• Paste the following formula as the 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. The INDIRECT function converts the F4 entry similar to the assigned names. Depending on F4 values, the INDIRECT function displays the product list.

• Click OK.
• You’ll get a dependent drop-down list.

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

Download the Excel Workbook

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