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.

Dataset-Excel Dependent Drop Down List with Spaces

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


Download Excel Workbook


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

In Excel’s Data tab, Excel offers the Data Validation feature within the Data Tools section. Users easily insert 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.

Cross-check-Excel Dependent Drop Down List with Spaces

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

=INDEX (array, row_num, [col_num], [area_num])

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

=MATCH (lookup_value, lookup_array, [match_type])

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).

index match-Excel Dependent Drop Down List with Spaces

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.

Data validation

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 appear as drop down list.

product 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.

=INDEX(B5:D13,,MATCH($F$4,$B$4:$D$4,0))

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.

Data validation

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.

index match result

Read More: How to Make a Drop-Down List Based on Formula in Excel (4 Ways)


Similar Readings


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

=INDIRECT (ref_text, [a1])

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).

indirect-Excel Dependent Drop Down List with Spaces

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.

New Name

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

New Name

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

Name Manager


🔁 Drop Down List

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

Data validation

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.

Data validation

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.

product list-Excel Dependent Drop Down List with Spaces


🔁 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.

Data validation

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

Product list-Excel Dependent Drop Down List with Spaces

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 Make a Drop Down List in Excel (Independent and Dependent)


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

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo