Google Sheets Dropdown Based on Another Cell: How It Works

Yes. In Google Sheets, you can make one dropdown list depend on the value in another cell by creating named ranges for the available choices and using an INDIRECT reference as the dependent list’s source. For a small set of categories, this is usually the clearest approach; helper ranges are a useful alternative when the category names or source data are less convenient.

What a dependent dropdown does

A dependent dropdown limits the choices in one cell according to a selection made elsewhere. For example, a user might choose a department in A2, then choose a role in B2. If A2 is “Sales,” B2 offers sales roles; if A2 is “Support,” B2 offers support roles. This is different from placing the same fixed dropdown in every row: the available items change with the parent cell.

Dependent lists are useful for order forms, project trackers, surveys, inventory sheets, and data-entry tables. They help guide people toward valid combinations and reduce spelling variations that make later sorting or analysis harder. They do not, by themselves, guarantee that the two selections remain consistent after someone edits the parent cell, so it is important to understand how changes behave.

Set up the source lists

First, organize the options in a way that makes their relationship clear. For example, place categories in one column and their associated choices in another on a separate sheet named Lists:

Lists!A2:A4: Sales, Support, and Operations.
Lists!C2:C4: Account Executive, Sales Analyst, and Sales Manager.
Lists!D2:D4: Help Desk, Technical Support, and Customer Success.
Lists!E2:E4: Coordinator, Operations Analyst, and Scheduler.

Keep each group of choices in its own contiguous range. A header can sit above each group, but do not include that header in the range that will appear in the dropdown. This layout makes it easier to check that each category points to the intended choices and to add new items later.

For the simplest named-range method, use category names that can also be used as named ranges. Avoid spaces and punctuation in those names. If the labels people should see need spaces, such as “Customer Support,” use a helper mapping or the helper-range approach described below rather than assuming that every visible label can serve directly as a range name.

Create the parent dropdown

In the entry sheet, select the cell or cells where users will choose the category, such as A2. In Google Sheets in a web browser, choose Data > Data validation, then select Dropdown (from a range) as the criterion. Enter the category range, for example Lists!A2:A4, and save the rule. The parent cell now offers the categories in that source range.

You can also type the category choices directly into a dropdown rule, but using a source range is easier to maintain when the list may change. If you add a category outside the range currently used by the rule, it will not automatically become an option unless the source range includes it. Allowing extra blank rows in the source range can make additions simpler, though blank entries may appear or behave differently depending on the validation setup.

Create named ranges for each category

For each category’s choices, select its cells on the Lists sheet and choose Data > Named ranges. Give the range a name that matches the corresponding category value. For instance, name the Sales roles range Sales, the Support roles range Support, and the Operations roles range Operations. Use the named-range panel to confirm that each name refers to the correct cells.

Named ranges give a readable name to a cell range, so a formula can refer to Sales instead of a sheet-and-cell address. The names must follow Google Sheets’ naming rules and cannot contain spaces or look like cell references. A mismatch between the parent cell’s text and the named range will prevent the dependent list from resolving correctly. Watch for leading or trailing spaces and inconsistent spelling as well as capitalization.

Connect the second dropdown to the parent cell

Select B2, the cell that should contain the dependent choices, and open Data > Data validation. Choose Dropdown (from a range) for the criterion and enter =INDIRECT($A2) as the range source. The reference points INDIRECT to the named range whose name is written in A2. Save the rule and test it by choosing each category in A2.

For a column of entries, set up and test the rule on the first dependent cell before applying it to additional rows. In the reference $A2, the dollar sign fixes the parent column while leaving the row relative. When you copy the validation rule down, each row should refer to its own category cell. Because validation dialogs and copied rules can be easy to misconfigure, test more than one row and confirm that changing A3 affects B3 rather than B2.

If Sheets rejects the range expression in your setup, or if the category text cannot match valid named ranges, use helper ranges instead. The essential constraint is that a dropdown sourced from a range needs an actual range of cells to display; a formula that produces choices may need to spill into helper cells first. Keep those helper cells on a separate sheet and hide that sheet only if doing so will not make maintenance confusing.

Use helper ranges when names are awkward

A helper-range design is often easier when category labels contain spaces, come from a data table, or need more flexible matching. Keep a two-column source table with category names in Lists!A2:A20 and choices in Lists!B2:B20. For a helper list associated with the category in A2 on the entry sheet, place this formula in an available helper cell, such as Lists!G2:

With categories in Lists!A2:A20, their choices in Lists!B2:B20, and the selected category in Entry!A2, enter =IFERROR(FILTER($B$2:$B$20,$A$2:$A$20=Entry!$A2),"") in Lists!G2. The formula returns the choices whose category matches Entry!A2; if there are no matches, it returns a blank rather than an error.

Then set the dependent dropdown’s source to the cells occupied by that helper result. A single helper column is suitable for a single entry row, but a sheet with many independently changing rows needs a deliberate helper layout: for example, a separate helper column or block for each entry row. Make sure each validation rule points to its own helper output. Do not point every row’s dropdown at one shared helper list if that list changes according to only one parent cell.

The helper method is more adaptable but takes more space and can be harder to maintain at scale. It also requires planning for the maximum number of matches, since the formula output needs room to expand without being blocked by other cell contents. Keep helper formulas away from notes or other data that could occupy those cells.

Choose between named ranges and helper lists

Use named ranges with INDIRECT when the number of categories is modest, each category has a clear fixed list, and category names can be made to match valid range names. Once set up, the rule is compact and the source lists are easy to inspect. The trade-off is that every category needs its own named range, and changes to category labels can break the link.

Use helper ranges when your choices live in a normalized table, labels contain spaces, or the list is generated from matching records. This design keeps the source data in a consistent table and uses a formula to assemble each list. The cost is additional helper cells and more care when assigning a different helper output to each row.

Google Sheets does not make the underlying source data disappear just because a dropdown is used. Users with permission to edit the sheet may still change the list or validation rule. Protect source or formula cells if accidental edits are a concern, and share the spreadsheet with appropriate access settings. Protection is an editing safeguard, not a substitute for limiting access to sensitive data.

What happens when the parent choice changes?

Changing A2 changes the options offered by the validation rule in B2, but it should not be assumed to erase a value already present in B2. For example, if A2 changes from Sales to Support while B2 contains “Account Executive,” that old value may remain even though it is no longer an available Support choice. The user may need to select a valid replacement.

In the data-validation rule, choose whether invalid data should be rejected or merely shown with a warning. Rejecting invalid input helps prevent new entries that are not in the current list, while a warning gives users more flexibility. Neither choice should be treated as an automatic cleanup process for values entered before the parent changed. If you need the child cell cleared whenever its parent changes, that requires an explicit workflow, such as manual clearing or an Apps Script trigger; a validation rule alone does not provide that behavior.

Common problems and fixes

The child dropdown is empty: Check that the parent cell exactly matches the named range, that the named range points to nonblank cells, and that the validation source uses the intended parent cell.
One row changes another row’s options: Inspect the row references in the validation rules. A fully fixed reference to A2 can make every row depend on the same category.
A category contains spaces: A named range cannot simply be assumed to work with a label such as “Customer Support.” Use a helper mapping to a valid range name or build a helper list based on the full label.
New choices do not appear: Confirm that the named range or source range includes the newly added cells. A fixed range will not necessarily expand just because data was entered beneath it.
An old choice remains after changing the category: Clear the dependent cell and choose again. The old value is not proof that the new list is wrong.
The source formula produces an error: Check spelling, sheet names, range addresses, and whether the helper output has room to expand. A cell occupying the intended spill area can block results.

How this differs from Excel

The instructions here are for Google Sheets in a web browser. Excel also supports dependent dropdowns, but its data-validation interface and some details of formula references differ, so do not assume that a Google Sheets validation source can be pasted unchanged into Excel. If a spreadsheet will be used in both programs, test the dropdowns in each application after conversion or import; named ranges, formulas, and validation rules can behave differently between them.

Frequently Asked Questions

Can I make a dropdown depend on another cell in Google Sheets without a script?
Yes. Named ranges with INDIRECT, or helper cells that produce a range of matching choices, can create dependent dropdowns without Apps Script. A script is only needed for extra behavior such as automatically clearing a child cell when its parent changes.

Why does changing the first dropdown not clear the second cell?
Data validation controls which new values are allowed or warned about; it does not reliably clear a value already in the cell when the parent selection changes. Clear the child value manually, or create a separate script-based workflow if automatic clearing is essential.

Can dependent dropdowns work for every row in a table?
Yes, but each row’s child validation needs to refer to that row’s parent. Test the first rule and copy or apply it carefully, then verify several rows. With helper lists, give each row its own helper output rather than using one output that follows only a single parent cell.

Do category names need to match named ranges exactly?
For the direct INDIRECT method, the parent cell’s text must resolve to the intended named range. Spaces and other characters can make a visible label unsuitable as a range name, so use a valid matching name or a helper mapping when labels do not fit.

Can I add more options later?
Yes. Add choices inside the source range used by the named range or helper formula, or update the range so it includes the new cells. Then check the dropdown to confirm the added choice is included and that no unrelated cells were added to its list.

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.