Google Sheets INDIRECT in Data Validation: How It Works

How-to basics · Updated November 20, 2026

Yes, you can use INDIRECT to help create dependent dropdowns in Google Sheets, but the reliable approach is to use it in a helper cell and point data validation at the resulting range. The Dropdown (from a range) field expects a cell range, so entering an INDIRECT formula directly there may be rejected; the helper method avoids that problem.

What INDIRECT Does in a Dropdown

INDIRECT turns text into a cell or range reference. That makes it useful when the range for one dropdown depends on a selection in another cell. For example, a person might choose a department in A2, then choose a team in C2. The available team names depend on the department.

A common setup assigns each department’s team list a named range. If A2 contains the text Sales, a formula using INDIRECT(A2) can refer to the named range called Sales. A helper cell can expose that range’s values, and the dropdown rule can use the helper cells as its source.

This is different from a regular dropdown whose choices always come from the same fixed list. It is also different from using a custom formula to decide whether an entered value is valid: a custom formula checks an entry, while a dropdown source supplies the choices that appear in the menu.

Why Use a Helper Range

In Google Sheets, the Dropdown (from a range) setting is designed to point to cells, such as Lists!A2:A10. It does not behave like Excel’s data-validation Source box in every respect, and formula text such as =INDIRECT(A2) may not be accepted as a range reference in Sheets. A helper range separates the formula calculation from the validation rule.

The helper range also makes the setup easier to inspect. You can see which choices the formula is returning before connecting it to a dropdown. If a choice is missing, you can check the source data, named range, or formula without having to guess what the validation rule is resolving.

For a single dependent dropdown, the helper list can sit in unused columns on the same sheet or on a separate helper sheet. You can hide helper columns after confirming the dropdown works. Do not delete the helper cells: the validation rule still depends on that range.

Set Up Named Ranges for Categories

Here is a concrete example. Suppose a sheet called Lists has departments in one column and their teams in separate columns: Sales teams in A2:A4 and Support teams in B2:B4. The category dropdown will use Sales or Support, and the second dropdown will show the matching team names.

  1. On Lists, enter each team list in a continuous range. Keep headings outside the ranges you intend to use as dropdown choices.
  2. In Google Sheets, open Data > Named ranges. Create the named range Sales for Lists!A2:A4, then create Support for Lists!B2:B4.
  3. Make sure the category text matches its named range exactly. Named ranges cannot contain spaces, so a category such as Customer Support would need a name such as Customer_Support, with a formula to convert the space to an underscore.
  4. Create the first dropdown by selecting A2 and choosing Data > Data validation > Add rule. Choose a dropdown sourced from the category cells, or enter the category choices directly.

Named ranges make the example easy to follow, but they are optional. You can also use a helper formula that filters a two-column category-and-item table. The named-range approach is convenient when each category already has its own list.

Use INDIRECT in a Helper Cell

Assume A2 contains a category that exactly matches a named range, and you want the matching choices to appear on a helper sheet beginning in B2. With those cell and named-range assumptions, enter this Google Sheets formula in Helper!B2:

=IFERROR(INDIRECT($A2),"")

Because the formula refers to a named range, it returns the values in that range. Leave enough empty cells below B2 for the longest category list to expand; existing content in the way can prevent the results from appearing. IFERROR keeps the helper area from displaying an error when A2 is blank or does not match a valid named range.

If category labels contain spaces but the corresponding named ranges use underscores, use this version instead. It assumes A2 contains the category label and that the named ranges replace spaces with underscores:

=IFERROR(INDIRECT(SUBSTITUTE($A2," ","_")),"")

For instance, if A2 says Customer Support, the formula looks for a named range called Customer_Support. This conversion only replaces spaces; it does not fix every possible mismatch. Punctuation, spelling, and extra characters still need to be handled consistently.

Connect the Helper Range to Data Validation

Once the helper values appear, select the cell for the dependent dropdown, such as C2, and open Data > Data validation > Add rule. Set the criteria to Dropdown (from a range) and enter the helper range, for example Helper!B2:B20. Adjust the endpoint to cover the longest list you expect. Save the rule, then test it by changing A2 and opening the dropdown in C2.

If the helper range is longer than the current list, unused cells may appear as blank choices or be ignored depending on the data and interface behavior. Keep the source range reasonably close to the maximum list length, and avoid including headings. If blank entries are a problem, arrange the source lists and helper area to minimize unused cells, or use a purpose-built helper area for each dependent dropdown.

The validation rule points to the helper cells; it does not evaluate the named range itself. That distinction matters when troubleshooting. If the dropdown is empty, first check whether the helper formula has returned values, then confirm that the validation rule points to the right cells.

Handling Several Rows of Dependent Dropdowns

A setup for A2 and C2 does not automatically provide independent helper results for every row. If users will select categories in A2, A3, A4, and so on, each dependent dropdown needs to use the category from its own row. One straightforward layout is to reserve a separate helper column or block for each row, with the formula referencing that row’s category. Then set each row’s validation rule to its corresponding helper range.

For example, the helper formula for a second input row should refer to A3 rather than A2. If the formula is copied, use a relative row reference such as $A2 so the column stays fixed while the row changes. Keep each formula’s output area clear to prevent one expanding list from overwriting another.

For a larger form, consider keeping category and item data in a tidy two-column table and using a separate filtered helper list for each input row. That can be easier to maintain than creating many named ranges. Test the layout with short and long lists, and remember that validation rules and helper formulas must be applied to the rows where people will enter data.

Common Problems and Fixes

  • The dropdown says the range is invalid: Check that the validation source is a cell range such as Helper!B2:B20, not formula text that the range field cannot interpret. Confirm that the sheet name and cell references are spelled correctly.
  • The helper formula returns an error: Check that the selected category matches a named range. If the formula replaces spaces with underscores, verify that the named range uses the same convention.
  • The list is blank or incomplete: Confirm that the helper formula has enough empty cells to return the full range, and that the named range includes all intended choices but not its heading.
  • The second dropdown does not change after the category changes: Check that its validation rule points to the helper cells controlled by the relevant category. A rule connected to a fixed list will not become dependent just because another cell changed.
  • A previous choice no longer fits: Changing the category does not necessarily clear a value already selected in the dependent cell. Recheck that cell after changing the category; users may need to remove or replace an outdated choice.

One useful distinction is that data validation helps guide entry but is not a full data-cleaning system. A pasted value or an existing value may need separate checking. For important records, consider adding a visible warning or review step rather than assuming the dropdown alone guarantees every row is consistent.

Alternatives and Excel Differences

If the list is small and does not change, use one ordinary dropdown rather than adding named ranges and helper formulas. If categories and options are stored together in a two-column table, a filtered helper list can be more maintainable than separate named ranges. For workflows that need complex behavior, Google Apps Script can create or update validation rules, but it adds code and maintenance; it is unnecessary for a basic dependent dropdown.

Excel users may recognize the named-range-and-INDIRECT technique because Excel data validation commonly uses a formula in its list Source field. Google Sheets has its own data-validation interface and range handling, so do not assume that an Excel instruction using =INDIRECT(A2) in the Source box transfers directly. In Sheets, using a helper range and selecting it as the dropdown source is the more dependable pattern described here.

Frequently Asked Questions

Can I put INDIRECT directly in Google Sheets data validation?
It is not the most reliable method. The Dropdown (from a range) field is intended for a range reference and may reject a formula such as =INDIRECT(A2). Put the formula in helper cells and use those cells as the dropdown source.

Do I need named ranges to make a dependent dropdown?
No. Named ranges are useful when each category has its own list and the category text can identify that list. A helper formula based on a category-and-item table is another option.

Will the dependent cell clear when I change the category?
Do not assume it will. A value selected earlier can remain even if it is no longer appropriate for the new category. Clear or replace it, or add a review step to catch mismatches.

Can I hide the helper sheet?
Yes. You can hide a sheet or helper columns after setup, but keep the referenced cells and formulas in place. If the helper range is moved or deleted, update the validation rule.

Why does my named range not work with INDIRECT?
Check for exact spelling and naming rules. If the category contains spaces, convert them consistently, such as replacing spaces with underscores in both the named range convention and the helper formula.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.