Function Problems

How to Create a Customer and Property Dependent Drop-Down List in Excel

Kushani Nimanthika Oct 14, 2026 870 views

Question details

The user needs to set up a dynamic dependent drop-down list where the options in the property list automatically change based on the specific customer selected in the first list.

How to Create a Customer and Property Dependent Drop-Down List in Excel

Product: Excel
Device & OS: not provided
Scenario: Filling out a service table that requires tracking properties linked to specific customers. The setup must support multiple service rows and automatically adapt to a growing source table.
Observed behavior: The user requires a structured method to link two data validation lists dynamically so the dependent list correctly filters the associated properties for each customer across multiple data entry rows.

Before you start

Ensure your source data for customers and properties is cleanly organized into distinct columns, without any blank cells in between, to allow for seamless Named Range creation.

Solution 1Recommended

Use Named Ranges and the INDIRECT Function

This is the most reliable and backward-compatible method for creating dependent drop-down lists across multiple rows using built-in Data Validation.

By assigning a specific name to each group of properties that exactly matches the customer's name, you can use the INDIRECT function to tell Excel to look for that specific named list whenever a customer is selected.

Define the primary Customer list

Select the unique list of customer names in your source sheet. Go to the Formulas tab, click 'Define Name', and name this range 'Customers'.

Create Named Ranges for Properties

Group your properties by customer. Highlight the properties for the first customer, go to 'Define Name', and enter the exact customer name as the range name. Repeat this for all customers. Alternatively, use 'Create from Selection' in the Formulas tab if your data is arranged in columns with customer names as headers.

Set up the primary drop-down

Select the cell or column where you want the Customer drop-down to appear (e.g., cell A2). Go to the Data tab, click 'Data Validation', choose 'List' under the Allow dropdown, and type '=Customers' in the Source box. Click OK.

Set up the dependent drop-down

Select the adjacent cell for the Property drop-down (e.g., cell B2). Open 'Data Validation', choose 'List', and in the Source box, type the formula '=INDIRECT(A2)'. Click OK. Now, the property list will only show items related to the customer selected in A2.

Use Named Ranges and the INDIRECT Function

Handling Spaces in Names: Named ranges cannot contain spaces. If your customer names include spaces, Excel replaces them with underscores when creating ranges. You must use the SUBSTITUTE function in your validation formula: =INDIRECT(SUBSTITUTE(A2," ","_")).

Easily Create Dynamic Drop-Down Lists with WPS Spreadsheets

WPS Spreadsheets provides an intuitive Name Manager and robust Data Validation tools, making it incredibly simple to set up dependent drop-down lists for your business data without complicated workarounds.

1. Organize your data: Open your workbook in WPS Spreadsheets. Place customer names as column headers and their respective properties in the rows below them.
2. Batch create Named Ranges: Highlight the entire data block, navigate to the 'Formulas' tab, and click 'Create from Selection'. Ensure 'Top row' is checked to automatically name your property ranges after the customers.
3. Apply Data Validation: Go to the 'Data' tab and click 'Validation'. Set up your primary list referencing your customer headers.
4. Use the INDIRECT formula: Apply Data Validation to your dependent property column, select 'List', and use the =INDIRECT() formula referencing the primary customer cell.

Fully compatible with Microsoft Excel's Data Validation, Named Ranges, and formulas (.xlsx format).Intuitive Name Manager interface to quickly organize and edit dynamic customer and property lists.Free, lightweight, and fast alternative to heavy spreadsheet software with an easy-to-use tabbed interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my INDIRECT formula return an error when setting up the dependent list?

This usually happens if the primary drop-down cell is currently empty when you are setting up the rule, or if the text in the primary cell contains spaces or special characters that don't perfectly match the defined Named Range. Excel will prompt an error warning, but you can usually click 'Yes' to continue.

How do I make the drop-down list update automatically when new properties are added?

Format your source data as an official Excel Table (using Ctrl+T) before defining your Named Ranges, or use dynamic formula functions like OFFSET and COUNTA in your Name Manager. This ensures that any new items added to the bottom of your source list automatically appear in the drop-down menu.

Can I use dynamic arrays like the FILTER function for dependent drop-downs instead?

Yes. In newer versions of Excel, you can bypass Named Ranges by using the FILTER function directly to extract properties that match the customer name. However, the INDIRECT method remains the most universally compatible approach across older versions and alternative spreadsheet software.

How can I clear the dependent drop-down cell automatically if the primary selection changes?

Spreadsheet software does not natively clear the dependent cell when the primary cell is changed. To achieve this automatically, you would need to implement a simple VBA macro using the Worksheet_Change event to clear the contents of the secondary cell whenever the first one is modified.

Excel & Data Problems