Excel Data Validation List Based on Another Cell Value

Yes. In Excel for Microsoft 365 on Windows, select the cell for the dropdown, then choose Data > Data Validation, set Allow to List, and enter a range in Source. If you mean a dropdown whose choices change according to a selection in another cell, use named ranges with INDIRECT, or use a helper formula with FILTER for a more flexible Microsoft 365 setup.

Two meanings of a list based on another cell

The phrase can describe two related but different setups. The simple version takes its list of choices from a range of cells: for example, a dropdown in B2 displays the values stored in F2:F5. The choices do not change when another cell changes; the range is simply stored elsewhere on the sheet.

The dependent version changes the available choices according to a value selected in a different cell. For example, a person chooses a department in A2, then sees only that department’s employees in B2. This is often called a dependent dropdown. It needs a formula or named range to connect the first selection to the second list.

Decide which behavior you need before setting up validation. A direct range is easier to maintain for a fixed list. A dependent list is useful when a long menu would otherwise contain irrelevant options, but it needs more careful setup.

Use a cell range as the dropdown source

For a basic list, put the options in a worksheet range, ideally in a dedicated area or a separate Lists sheet. Suppose the choices are in F2:F5 and the dropdown belongs in B2. Select B2 and use these Windows steps:

Open Data > Data Validation.
On the Settings tab, choose List in the Allow box.
In Source, enter =$F$2:$F$5.
Make sure In-cell dropdown is selected, then choose OK.

The dollar signs make the source range absolute, so it will not shift if you copy the validation to another cell. To apply the same dropdown to several cells, select the whole destination range before opening Data Validation. If the source cells are on another worksheet, Excel may reject a direct cross-sheet reference in the Source box. A workbook-level named range is a straightforward workaround: define a name such as ProductChoices for the source cells, then use =ProductChoices as the validation source.

In Excel for Mac, the corresponding command is also on the Data tab as Data Validation, though the dialog layout can differ. Excel for the web has data-validation controls too, but the Windows desktop steps above are the clearest route for configuring and troubleshooting more involved formulas.

Create a dependent dropdown with named ranges

A traditional dependent dropdown uses one named range for each parent choice. Imagine A2 contains a department selection and B2 should list that department’s employees. On a Lists sheet, put employee names in separate columns, with a heading above each column. Use headings such as Sales and Support, and define workbook-scoped names Sales and Support that refer to the corresponding employee ranges. Names cannot contain spaces, so use underscores in names when needed.

Create the first dropdown in A2 using the department names as its source. Then select B2 and open Data > Data Validation. Choose List for Allow. With the department selection in A2 and named ranges that match the department labels, use this Source formula:

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

SUBSTITUTE changes spaces in a selected label to underscores so that a label such as Customer Support can point to a named range called Customer_Support. If your labels already match valid range names exactly, the shorter formula =INDIRECT($A2) works. For copied validation, the dollar sign locks the column A reference but leaves the row relative, so a dropdown in B3 can refer to A3.

To create names, select each group of choices and use Formulas > Define Name, or open Formulas > Name Manager and choose New. Give each name a clear, valid name and check that Refers to points to the intended cells. If you use worksheet-scoped names, be aware that INDIRECT must resolve the name in the relevant context; workbook-scoped names are usually simpler for this pattern.

Build a changing list from a table with FILTER

Named ranges are convenient for a small number of categories, but maintaining a separate range for every category can become awkward. In Microsoft 365 or Excel 2021, FILTER can produce a list from a two-column source table. For example, put categories in A2:A100, corresponding choices in B2:B100, the category dropdown in D2, a helper formula in F2, and the dependent dropdown in E2.

With those cell locations, enter this formula in F2:

=FILTER($B$2:$B$100,$A$2:$A$100=$D$2,"")

FILTER spills matching choices into cells below F2. Create a workbook-level name, such as CurrentChoices, in Formulas > Name Manager and set Refers to to =Sheet1!$F$2#, replacing Sheet1 with the actual helper-sheet name. Then set the validation Source for E2 to =CurrentChoices. The # refers to the whole spilled range, so the dropdown can follow the number of matches as the category changes.

Keep the cells below the helper formula clear; a value blocking the spill causes a #SPILL! error. FILTER returns every match, including duplicates. If you want distinct, sorted options, a Microsoft 365 or Excel 2021 formula can combine FILTER, UNIQUE, and SORT, but the helper result still needs to spill into an unobstructed area. This approach is best when your source is a growing category-and-choice table rather than a set of manually maintained columns.

Choose the method that fits the workbook

Direct range: Best for one fixed list, such as status values or a short set of regions. It is easy to understand and has few moving parts.
Named ranges and INDIRECT: Useful for a modest number of categories with stable lists. It is widely used and works without FILTER, but names must correspond to the parent choices and need maintenance.
FILTER and a spill range: Useful when choices live in a table and change over time. It reduces duplicated list maintenance, but needs a supported Excel version, a helper area, and a name that refers to the spill.

For a workbook shared with people using older Excel releases, favor the simplest method that meets the need. FILTER requires Excel 2021 or Microsoft 365; features such as dynamic arrays may not behave as expected in older versions. Also consider whether users need to add new categories themselves. A structured source table and a documented place for new entries can make later updates safer than hard-coded, scattered ranges.

Common problems and how to fix them

The dropdown is empty: Check that the source range contains values and that the name points to the right sheet and cells. For a dependent list, confirm that the parent cell’s text matches the named range after any space-to-underscore conversion.
Excel says the source evaluates to an error: Check for a misspelled name, an invalid name containing spaces, or a formula that returns an error. INDIRECT relies on text that resolves to a valid reference; a category with no matching name will not produce a valid list.
The dropdown does not update as expected: A dependent cell may keep an old selection even after the parent changes. Data Validation limits future entries; it does not automatically erase a value already in the cell. Clear the dependent cell or add a separate review process if stale selections would be misleading.
The FILTER helper shows #SPILL!: Clear cells in the spill area and check for merged cells or other obstructions. Make sure the formula is outside an Excel table if its spill range would overlap table content.
Some entries are missing: Inspect the source for extra spaces, inconsistent spelling, or blanks. Text comparisons can fail when labels differ, and blank cells in a list may appear as blank dropdown options.
Validation is easy to bypass: A user can paste data into a validated cell, potentially replacing its validation or inserting an unlisted value. Use error alerts to discourage invalid typing, and check important data separately rather than assuming the dropdown guarantees clean records.

Keeping lists reliable as the workbook grows

Use clear headings and keep source values in one maintained place. Avoid putting explanatory notes or blank rows inside the source range, since those may become selectable items. For a direct list that grows often, Excel Tables can make source data easier to extend, although a validation Source field may need a name or another reference arrangement rather than a structured table reference entered directly.

In the Data Validation dialog, the Error Alert tab lets you choose whether Excel should stop an invalid entry, warn the user, or merely show information. A Stop alert is appropriate when the list is intended to restrict entries, but it is not a substitute for checking data pasted into the sheet. The Input Message tab can explain what a user should choose. These small instructions are valuable in shared templates where the purpose of a dropdown is not obvious.

Frequently Asked Questions

Can a data validation list refer to another worksheet?
Yes. A direct cross-sheet reference in the Source box may be rejected, so define a name for the range on the other sheet and use that name, such as =ProductChoices, as the source.

How do I make a dropdown depend on the value in the cell beside it?
Set up a named range for each parent choice, then use a validation formula such as =INDIRECT($A2) in the neighboring dropdown’s Source, adjusting the cell reference to your layout. The parent text must resolve to a valid range name.

Does changing the first dropdown clear the second one?
No. Data Validation controls what can be entered, but it does not automatically clear a previously selected value when its parent changes. Users may need to reselect the dependent value, or a separate macro or process would be needed to clear it automatically.

Can I use FILTER for a dependent dropdown?
Yes, in Microsoft 365 or Excel 2021. Put a FILTER formula in a helper cell, define a name that refers to the formula’s spilled range with #, and use that name as the validation source.

Why does INDIRECT return an error?
Most often, the selected text does not match a defined name, the name is misspelled, or the name refers to an invalid range. Check spacing and underscores in labels, and confirm the name in Formulas > Name Manager.

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.