Function Problems

How to Create Dependent Drop-Down Lists in Excel Using Company and Name

WPS Editor Sep 25, 2026 869 views

Question details

The user needs to set up dependent data-validation lists in Excel that display specific activity options based on the selected Company and Name values.

How to Create Dependent Drop-Down Lists in Excel Using Company and Name

Product: Excel
Device & OS: not provided
Scenario: Creating multi-level data validation where the second drop-down's options dynamically depend on the selection made in the first drop-down.
Observed behavior: The user needs the correct formula and data structuring approach to ensure activity options correctly match the selected Company and Name.

Before you start

Ensure your source data is organized so that rows belonging to the same category (e.g., Company) are adjacent to each other, even if they aren't strictly sorted alphabetically.

Solution 1Recommended

Use OFFSET and MATCH Formulas for Dependent Drop-Downs

This method uses dynamic array formulas to fetch corresponding options based on grouped source data for your dependent drop-down lists.

For this formula to work properly, the source values do not have to be alphabetically sorted, but rows belonging to the same company must be adjacent to one another (for example, A3, A3, A1, A1).

Additionally, if your sheet names contain spaces or punctuation marks, you must wrap them in single quotation marks within the formula. Sheet names without spaces do not require them.

Select the Target Cell

Click on the cell where you want the dependent drop-down list to appear.

Open Data Validation

Navigate to the Data tab on the Excel ribbon and click on Data Validation.

Choose List Validation

In the Data Validation dialog box, go to the Settings tab and select 'List' from the Allow drop-down menu.

Enter the OFFSET Formula

In the Source box, enter the formula: =OFFSET(Master!$B$1,MATCH(A2,Master!$A$2:$A$12,0),0,COUNTIF(Master!$A$2:$A$12,A2),1). Be sure to replace A2 with the cell reference of your primary drop-down.

Apply and Test

Click OK to apply the validation. Test the primary and secondary drop-downs to ensure the matching activity options display correctly.

Use OFFSET and MATCH Formulas for Dependent Drop-Downs

Cell References: Ensure you use the correct relative or absolute reference cell (like A2) instead of a fully qualified reference (like Sales!J2) if you are working within the same active sheet.

Create Dynamic Lists Easily in WPS Spreadsheet

Use WPS Spreadsheet for Advanced Data Validation

WPS Spreadsheet fully supports complex formulas like OFFSET, MATCH, and COUNTIF to create dynamic, dependent drop-down lists. It is highly compatible with Microsoft Excel and offers a seamless experience for complex data management tasks.

1. Open your workbook: Launch WPS Spreadsheet and open the workbook containing your company and name data.
2. Group your data: Organize your source data so that related categories, like Company names, are adjacent in their rows.
3. Access Data Validation: Select the cell for your dependent drop-down, navigate to the Data tab, and click 'Validation'.
4. Input the formula: Choose 'List' under the Allow settings and paste your OFFSET and MATCH formula into the Source field.
5. Save and verify: Click OK to create the dependent drop-down menu, then verify that the options populate based on your first selection.

100% compatible with Microsoft Excel file formats (.xlsx, .xls)Supports advanced dynamic formulas and multi-level data validationLightweight, fast, and free to useIntuitive interface familiar to Microsoft Office users

microsoft office alternative - wps office

Frequently Asked Questions

Do my Company values need to be alphabetically sorted for the OFFSET formula to work?

No, they do not need to be sorted alphabetically. However, rows belonging to the same company must be adjacent to each other so the COUNTIF function can correctly group them.

Why is my OFFSET formula returning an error for the sheet name?

If your sheet name contains spaces or punctuation marks, it must be enclosed in single quotation marks (e.g., 'Master Data'!$A$1). Sheet names without spaces or special characters do not require single quotes.

How do I handle dependent drop-downs if my source data values are not adjacent?

If your values are not adjacent, you should first sort the source data column by Company to group identical items together. Alternatively, in newer software versions, you can use the UNIQUE and FILTER functions to dynamically extract matching activities regardless of the row order.

Can I create a three-level dependent drop-down list?

Yes, you can create multi-level dependent lists by setting up multiple named ranges or using nested INDIRECT or FILTER formulas, where each subsequent drop-down references the selected value of the drop-down immediately preceding it.

Excel & Data Problems