Dynamic dropdown lists are one of the highest-leverage things you can build in Google Sheets — they keep input clean, prevent typos, and let downstream formulas trust the data they receive. This tutorial walks through three setups: a simple list, a dependent (cascading) dropdown, and a fully dynamic list driven by a UNIQUE/SORT formula.

Simple dropdown from a range

Open Data → Data validation → Add rule. Set Criteria to "Dropdown (from a range)" and point it at a single column on a hidden Lists tab. Save the rule and every selected cell becomes a dropdown. The list stays in sync with the source range — add a value to the source and it appears in the dropdown immediately.

Dependent dropdowns with INDIRECT

When the second dropdown should depend on the first (Region → Country, Department → Team), build a small Lookup tab where each region has its list of countries in a named range like Countries_NA, Countries_EU. Set the second cell's data validation criteria to =INDIRECT("Countries_" & B2). INDIRECT resolves the named range at evaluation time, so changing B2 swaps the dropdown. The trade-off is that INDIRECT-based validation is fragile — you cannot ARRAYFORMULA it down a column, and copying the rule to other cells requires care.

Fully dynamic, deduped lists

If the dropdown options should reflect the live state of a transactions sheet ("every customer that has at least one order this year"), build the list with a formula on a hidden tab: =SORT(UNIQUE(FILTER(Orders!B:B, YEAR(Orders!C:C) = 2025))). Point the data validation rule at that helper column. The rule reads only the populated rows, so the dropdown grows and shrinks automatically as the source changes.

Worked example: dependent country picker

Set up a Lists tab with Regions in column A and Country lists in named ranges. In your form sheet, validate column B against the regions list and column C against =INDIRECT("Countries_" & B2). Pick "North America" in B2 and the C2 dropdown shows USA, Canada, Mexico; switch to "Europe" and it shows the European list. Combine with conditional formatting to gray out incompatible rows.

Worked examples

Simple list-from-range dropdown

Data validation criteria: =Lists!A2:A

Open-ended range so new entries appear automatically.

Dependent dropdown with INDIRECT

Data validation criteria: =INDIRECT("Countries_" & B2)

Named ranges named after the parent value.

Live, deduped list of customers

On Lists tab: =SORT(UNIQUE(FILTER(Orders!B:B, YEAR(Orders!C:C)=2025)))

Validation points at the formula's spill range.

Common mistakes (and how to avoid them)

  • Naming dependent ranges with characters Sheets does not allow (spaces, hyphens). Stick to letters, numbers, and underscores.
  • Forgetting that INDIRECT-based validation cannot be ARRAYFORMULA-spilled. Build the rule cell by cell or accept the limitation.
  • Pointing validation at a fixed range like A2:A100 — the dropdown silently caps at row 100. Use open-ended ranges.
  • Allowing free input. Set the rule to "Reject input" if you want true validation; "Show warning" lets bad values through.

Tips that pay off later

  • Hide your Lists tab once stable to discourage accidental edits.
  • Use conditional formatting on the validated cells to color the picked option, making the form sheet easier to scan.
  • If you need multi-select, switch to a checkbox column or build the UI in Apps Script — native data validation is single-select.

When to reach for something else

If your form has more than two cascading levels, consider building it in a Google Form or Apps Script sidebar — chained INDIRECT validations get hard to maintain quickly.

Functions used in this tutorial

INDIRECT → UNIQUE → FILTER → SORT → ARRAYFORMULA →