How to Create Dependent Drop-Down Lists in Google Sheets using the INDIRECT Function

The Need for Dynamic Drop-Downs

Data Validation drop-down menus are essential for keeping Google Sheets clean and preventing users from making typos during data entry. However, a static drop-down list is often insufficient. For example, if you have a spreadsheet tracking vehicle inventory, you might have one drop-down for “Car Make” (Toyota, Ford, Honda). When a user selects “Toyota,” you want the second drop-down in the next column for “Car Model” to automatically update to only show Toyota models (Camry, Corolla, RAV4), hiding all the Ford and Honda models.

This is called a Dependent Drop-Down list. While it seems complex, you can build this natively in Google Sheets without any Apps Script coding by using Named Ranges and the incredibly powerful INDIRECT function.

Step 1: Set Up the Reference Data

To make this work, you must organize your background data precisely.

1. Open your Google Sheet and create a new tab. Name it “Data”. We will hide this tab later.
2. In Row 1, list your primary categories across the columns. For our example:
   • A1: Toyota
   • B1: Ford
   • C1: Honda
3. Beneath each header, list the specific models.
   • Under Toyota (A2:A4): Camry, Corolla, RAV4
   • Under Ford (B2:B4): F-150, Mustang, Explorer
   • Under Honda (C2:C4): Civic, Accord, CR-V

Step 2: Create Named Ranges

The INDIRECT function relies on Named Ranges. You must name the lists of models exactly the same as the header above them.

1. Highlight the Toyota models (A2:A4). Do not include the header in A1.
2. In the top menu, click Data > Named ranges.
3. A sidebar opens on the right. Name the range exactly: Toyota. Click Done.
4. Highlight the Ford models (B2:B4). Click “Add a range” in the sidebar. Name it exactly: Ford. Click Done.
5. Highlight the Honda models (C2:C4). Add a range and name it exactly: Honda. Click Done.

Crucial Rule: Named ranges cannot contain spaces. If your category is “Aston Martin”, your header and named range must be written as “Aston_Martin” for this trick to work.

Step 3: Create the Primary Drop-Down

Now, let’s build the user interface.

1. Switch back to your main working tab (e.g., “Sheet1”).
2. Click on cell A2 (this will be our “Make” selector).
3. Click Data > Data validation.
4. Under Criteria, select Drop-down (from a range).
5. Click the grid icon and select the headers from your Data tab (Data!A1:C1).
6. Click Done. You now have a drop-down in A2 that lets you pick Toyota, Ford, or Honda.

Step 4: The Magic of the INDIRECT Function

Now we will build the dependent drop-down in cell B2. We want B2 to look at whatever word is currently sitting in A2, recognize that word as a Named Range, and pull in the corresponding list.

1. Click on cell B2 (this will be our “Model” selector).
2. Click Data > Data validation.
3. Under Criteria, select Drop-down (from a range).
4. In the box where you would normally select a range, type the following formula exactly:
   =INDIRECT(A2)
5. Click Done.

How It Works

Go test it. Select “Toyota” in cell A2. When you click the drop-down in cell B2, it will only show Camry, Corolla, and RAV4.

Change A2 to “Ford”. The drop-down in B2 will instantly update to show F-150, Mustang, and Explorer.

The INDIRECT function takes a text string (like the word “Ford” sitting in cell A2) and converts it into a functional cell reference. Because we created a Named Range called “Ford”, the Data Validation tool parses the word as a command to go fetch the contents of that specific named range.

Conclusion

Dependent drop-downs are the hallmark of professional spreadsheet design. By combining Data Validation with Named Ranges and the INDIRECT function, you can force users down specific decision trees, guaranteeing data accuracy and creating an intuitive, app-like experience directly within Google Sheets.