How to Create Dependent Drop-Down Lists in Google Sheets Using INDIRECT and Data Validation

When collecting data in a spreadsheet, standard drop-down menus often display too many irrelevant options. For example, if a team member selects a country such as United Kingdom in the first cell, the second menu should only present relevant cities like London, Manchester, or Edinburgh, rather than a monolithic list of global locations. Creating a dependent drop-down list (also called a cascading or conditional list) ensures users only select valid, context-aware options, substantially eliminating human data-entry errors.

Google Sheets makes building multi-tier dependent menus straightforward by pairing named cell ranges with the INDIRECT function and native data validation rules.

Structuring Your Source Lookup Tables

Before applying validation criteria, you must structure your lookup data logically on a dedicated reference tab. Placing source lists on a separate worksheet keeps your primary data-entry sheet clean and prevents accidental overwrites.

1. Open your spreadsheet and click the Add Sheet (+) button at the bottom left to create a new tab. Rename this tab to Source_Data.
2. In row 1, enter your primary category headers across columns. For example:
   • Cell A1: Department
   • Cell B1: Engineering
   • Cell C1: Marketing
   • Cell D1: Finance
3. Populate each column with its corresponding secondary items:
   • Under Engineering (B2:B5): Frontend, Backend, DevOps, QA
   • Under Marketing (C2:C4): Content, SEO, Paid Media
   • Under Finance (D2:D4): Accounting, Payroll, Auditing

Creating Named Ranges for Secondary Lists

The INDIRECT function retrieves cell ranges dynamically by evaluating text strings. For this mechanism to work smoothly, each secondary column range must have a defined name that precisely matches the primary category header text.

1. Highlight the items under your first department (cells B2:B5 for Engineering).
2. Navigate to the top menu and click Data → Named ranges.
3. In the right-hand panel, set the range name to exactly match the header: Engineering. Click Done.
4. Repeat this exact process for each subsequent category:
   • Highlight C2:C4 and name the range Marketing.
   • Highlight D2:D4 and name the range Finance.

Note on multi-word categories: Google Sheets named ranges do not permit spaces. If your primary category includes spaces (such as Human Resources), replace spaces with underscores (Human_Resources) in both the header and the named range.

Setting Up the Primary Drop-Down Menu

Now switch to your active data-entry tab (for example, Sheet1) to configure the first drop-down menu.

1. Select the cell or column where users will choose the primary category (such as cell A2).
2. Click Data → Data validation → Add rule.
3. In the Criteria dropdown, choose Drop-down (from a range).
4. Click the grid icon and select the primary categories from your lookup tab: =Source_Data!B1:D1.
5. Click Done. Cell A2 now displays an interactive menu containing Engineering, Marketing, and Finance.

Configuring the Dependent Secondary Drop-Down

To populate the secondary menu based on cell A2’s selection, use a helper cell to generate the dynamic list before binding it to validation.

1. On your Source_Data tab, designate cell F1 as your helper formula cell.
2. Enter the following formula into cell F1:

=INDIRECT(SUBSTITUTE(Sheet1!A2, " ", "_"))

3. When cell A2 on Sheet1 contains Engineering, the formula instantly pulls the items from the Engineering named range into column F. If the selection changes to Marketing, column F updates dynamically.
4. Return to Sheet1 and select the secondary input cell (B2).
5. Click Data → Data validation → Add rule.
6. Select Drop-down (from a range) and enter the helper range: =Source_Data!F1:F10.
7. Click Done. Cell B2 now displays only the roles relevant to the department chosen in cell A2.

Preventing Invalid Selections When Categories Change

If a user selects Engineering in column A and DevOps in column B, but subsequently changes column A to Finance, column B may retain DevOps until manually updated. To alert users to mismatched entries:

• In the Data validation rule settings for column B, expand Advanced options.
• Under If the data is invalid, choose Reject the input or select Show a warning to display an orange flag on orphaned selections.
• Provide custom helper text, such as: “Please select a sub-role that belongs to the chosen department.”