Excel Data Validation Based on Another Cell: How It Works

Yes. In Excel for Microsoft 365, you can make data validation depend on another cell by using a custom formula or a dependent drop-down list; for a dependent list, create named ranges and set the list source to =INDIRECT($A2). Go to Data > Data Validation to set up either kind of rule.

What data validation based on another cell means

Data validation limits what someone can enter in a cell. A rule based on another cell makes that limit conditional: the permitted value or list changes according to a selection, number, date, or other value elsewhere in the row. For example, choosing a department in one cell can determine which job titles are available in the next.

There are two common goals, and they use different validation settings:

Dependent drop-down: The choices in one cell come from a list selected by another cell. Use Data Validation with Allow set to List.
Conditional entry rule: The cell accepts or rejects an entry according to a condition involving another cell. Use Allow set to Custom and enter a formula that returns TRUE for valid entries.

These rules guide data entry, but they are not a substitute for checking a dataset. In particular, validation does not automatically repair existing values, and pasted or imported data may not be stopped in the same way as a normal typed entry.

How a dependent drop-down works

A dependent drop-down uses a value in one cell to point Excel to a corresponding list. Suppose A2 contains a category and B2 should show the choices for that category. If the category is Fruit, B2 should list fruit; if it is Drinks, B2 should list drinks. Excel’s INDIRECT function turns the text in A2 into a reference to a named range.

For this approach, the category text must match the name of its list. If A2 contains Fruit, define a workbook-level named range called Fruit that refers to the fruit choices. The validation source in B2 can then use =INDIRECT($A2). The dollar sign fixes the category column while leaving the row relative, so the rule can be applied down column B.

This is a useful setup for forms where people make a sequence of related choices: country and region, product type and product, or project and task. It is a classic Excel method because it works with ordinary named ranges and does not require a macro.

Set up a dependent list in Excel

Here is a small example. Put the categories Fruit and Drinks in a worksheet, and put each category’s choices in its own column. Use simple list entries such as Apple, Pear, and Orange for Fruit, and Water, Tea, and Coffee for Drinks. Avoid blank cells within a list if you do not want a blank choice to appear.

Select the cells containing the Fruit choices. Choose Formulas > Define Name, enter Fruit as the name, and confirm that the reference points to the intended cells. Repeat for the Drinks choices, naming that range Drinks. Names cannot contain spaces, so use labels such as Hot_Drinks if needed.
In A2, create the category drop-down: select A2, choose Data > Data Validation, set Allow to List, and enter the category cells as the source. If the category list is on another sheet, a named range can make the source easier to manage.
Select B2 and choose Data > Data Validation. Set Allow to List and enter =INDIRECT($A2) in Source. Confirm the dialog, choose a category in A2, and check that B2 offers the matching choices.
To apply the dependent rule to more rows, copy B2 and use Paste Special > Validation on the destination cells, or select the full destination range before creating the rule. Check that the source reference adjusts by row, for example to =INDIRECT($A3) in the next row.

In Microsoft 365 for Windows, the menu path is Data > Data Validation. Excel for Mac has a Data Validation command on the Data tab as well, though the dialog layout can differ. Excel for the web also provides data-validation options, but if a workbook’s rule is difficult to edit there, open it in the desktop app to use the full dialog.

Use a custom formula for conditional entries

Not every rule needs a drop-down. A custom validation formula is appropriate when the cell should accept a value only if a logical test involving another cell is true. For example, perhaps column A contains Yes or No, and column B must be filled in when A says Yes.

Assume the status is in A2 and the required response is in B2. Select B2, open Data > Data Validation, set Allow to Custom, and enter =OR($A2<>"Yes",$B2<>"") in Formula. The rule permits any entry when A2 is not Yes; when A2 is Yes, it permits the entry only if B2 is not blank. Apply the rule to the other rows with the row reference left relative.

Custom formulas should evaluate to TRUE for an allowed entry and FALSE for a disallowed one. The formula is a validation test, not a formula that calculates the cell’s value. You can also configure an input message to explain what is expected, and an error alert to tell someone why an entry was rejected. A Stop-style alert is the strongest prompt for preventing an invalid typed value; Warning and Information alerts allow the user to continue.

Choose the method that fits the job

Use a dependent list when users should choose from a controlled set of values and the available choices vary with a prior selection. Use a custom formula when the rule concerns a relationship, such as requiring a value, limiting a number, or checking that two dates are in the right order.

For a drop-down, a list source based on INDIRECT is straightforward when category labels can match valid range names. It becomes awkward when labels contain spaces, punctuation, or spelling variations. A simple label-to-name convention avoids many problems; for instance, use a category such as Hot_Drinks if the corresponding defined name is Hot_Drinks.

For a conditional rule, write the logic around the actual input cells and decide how blanks should behave. A test that treats an empty prerequisite as valid may be suitable for an optional form, but not for a process where a category must be selected first. Explicitly deciding that behavior helps avoid a rule that appears to work but accepts incomplete records.

Common problems and how to fix them

The dependent list shows an error or no choices. Check that the category text exactly matches a defined range name, including spaces and punctuation. Confirm that the named range refers to the intended cells and that it is available to the worksheet where the validation is used.
The same list appears in every row. Check the source reference. For a rule down column B, $A2 fixes the category column but lets the row change. A reference such as $A$2 fixes both and makes every row depend on A2.
A category contains spaces. A range name cannot contain spaces. Either use a category label without spaces, create a mapping between display labels and range names, or choose a different list design.
Changing the first selection leaves an old second selection behind. Validation changes the choices offered next time; it does not clear a value that was already entered. If someone changes Fruit to Drinks, an earlier value such as Apple can remain in B2 even though it is no longer a valid choice. A separate review or automation is needed to clear or flag it.
Pasted values do not follow the rule. Data validation is mainly an entry aid, not a security boundary. Pasting, imports, or other edits can introduce values that were not selected from the list. Add a separate audit check when clean data is important.

Maintenance and alternatives

Named ranges are easy to understand but need maintenance if the source lists change. If a list grows, make sure its named range includes the new entries. You can inspect or edit names through Formulas > Name Manager. For a small form with a few stable categories, this upkeep is often reasonable; for a large, frequently changing catalogue, a more structured design may be easier to manage.

One alternative is to keep category and choice pairs in a single source table, then build helper ranges or formulas to prepare the relevant choices. This can be more scalable, especially when many categories are added over time, but it requires more setup and careful testing of the workbook’s dynamic behavior. Another option is to use a form or database where dependent fields and required-value rules are built into the data-entry process.

Whichever method you choose, document the allowed values, test each category, and check both valid and invalid cases. Also decide how to handle blank prerequisites and changes to earlier choices. Those decisions matter as much as the validation formula itself.

Frequently Asked Questions

Can data validation in Excel refer to another cell?
Yes. A custom validation formula can test another cell, and a List rule can use a reference derived from another cell. For a dependent list with named ranges, a typical source is =INDIRECT($A2).

Can I make a drop-down list change based on another drop-down?
Yes. Create a named range for each parent choice, make the parent cell a list, and use INDIRECT in the child cell’s List source. The parent’s displayed value needs to match the relevant defined name.

Why does my dependent drop-down show the wrong choices?
Check the category spelling, the defined name, the range it refers to, and whether the source formula fixes the wrong row. Also remember that changing the parent cell does not automatically erase a previously selected child value.

Can data validation require a cell only when another cell says Yes?
Yes. Use Allow: Custom and a formula that returns TRUE when the entry is acceptable. For example, the formula above permits a blank response unless the related status cell contains Yes.

Does validation stop someone from pasting invalid data?
Do not rely on it as a guarantee. Validation helps guide ordinary data entry, but pasted or imported content can bypass the intended choices. Use a separate check or controlled intake process if invalid data would cause a serious problem.

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.