Excel Dropdown From Another Sheet: Setup and Fixes

Yes. In Excel for Microsoft 365 on Windows, create a workbook-level named range for the cells on the other sheet, then use that name as the list source: select the destination cells, choose Data > Data Validation, set Allow to List, and enter the name in Source. For example, name the values in Lists!A2:A10 DepartmentList, then use =DepartmentList as the source.

Why a named range is the usual solution

Excel’s Data Validation list source field does not accept an ordinary direct reference to a range on a different worksheet. Typing =Lists!$A$2:$A$10 into Source may produce an error because the reference crosses sheets. A defined name provides a simple bridge: the name points to the source cells, and validation refers to the name rather than directly to the other sheet.

This approach is useful when the input sheet should stay uncluttered. You can keep approved departments, product names, regions, or status options on a separate sheet, while users select those values from a dropdown in the working sheet. The list remains centrally maintained, so you do not need to type the options into every validation rule.

Use a workbook-scoped name if the dropdown may be needed on several sheets. A sheet-scoped name is limited to its worksheet and can create confusing results when the same name exists in different places. Names cannot contain spaces; a short descriptive name such as DepartmentList is easier to maintain than a generic name such as List1.

Create a named range for the source list

Put the options in one column on a source sheet. For this example, add a heading in Lists!A1 and the choices in Lists!A2:A10. Avoid blank rows within the options.
Select the option cells, or open Formulas > Define Name to create a name directly.
In the New Name dialog, enter DepartmentList as the name and set Scope to Workbook.
With the source entries in Lists!A2:A10, set Refers to to =Lists!$A$2:$A$10, then confirm. If the sheet name contains spaces, Excel uses single quotes around the sheet name, as in ='My Lists'!$A$2:$A$10.

The dollar signs make the range absolute, so it does not shift when copied or used elsewhere. You can review or change names later through Formulas > Name Manager. Check both the name and its Refers to range if a dropdown stops showing the expected choices.

Apply the dropdown to cells

Go to the worksheet where people will enter data and select the destination cell or range, such as Entry!B2:B100.
Choose Data > Data Validation. In the Settings tab, choose List in the Allow box.
In Source, enter =DepartmentList. Make sure In-cell dropdown is checked, then choose OK.
Click a destination cell. The dropdown arrow typically appears when the cell is selected; use it to choose an option.

Applying validation to a whole intended entry range at once is usually easier than copying it row by row. If you want users to be able to leave a cell empty, keep Ignore blank selected. You can also use the Input Message tab to show a brief instruction when a cell is selected, and the Error Alert tab to display a message when someone enters an unlisted value.

Keep the choices current as the list grows

A fixed name such as DepartmentList points only to the cells you specified. If you add another department in A11 while the name still refers to A2:A10, that new item will not appear. You can edit the name in Name Manager to include A11, but remembering to update the range is easy to overlook.

For a list that changes often, one option is to make the source data an Excel Table. Select the heading and options, then choose Insert > Table and confirm that the table has headers. Give the table and its column clear names, such as Table1 and Department. A defined name can refer to the table column, while validation continues to use the defined name. For a table named Table1 with a column header Department, the name’s Refers to entry can be =INDIRECT("Table1[Department]"). Set the validation Source to =DepartmentList as before. New table rows are included in the column reference.

Another approach is to use a dynamic-array result on the source sheet and name its spill range. For example, if a formula spills its choices starting at A2, a name can refer to =Lists!$A$2#. The spill operator and dynamic-array functions require Microsoft 365 or a supported newer Excel version. If compatibility with older Excel versions matters, use a fixed range and extend it as needed, or test the workbook in the versions your recipients use.

Whichever method you choose, decide how to handle duplicates and blanks. A dropdown displays the source values; it does not automatically remove repeated options or sort them. Keep the source list tidy, and do not put notes or unrelated values in the range or table column.

What validation does—and does not—enforce

A dropdown makes approved choices easier to enter, but it is not a security boundary. A Stop-style error alert can reject an invalid value typed directly into the validated cell, but copying and pasting, filling cells, or writing a formula can bypass validation in some situations. Existing entries also are not automatically rewritten when you remove or rename an option in the source list.

If the workbook needs consistent data, consider checking the completed entries as well as providing a dropdown. For example, filter the column for unexpected values or use a separate review process before relying on the data. Protecting a worksheet can reduce accidental changes to the source list or validation cells, but sheet protection should not be treated as strong protection for confidential information.

Common problems and practical fixes

The source is rejected: Confirm that the validation Source is =DepartmentList, not a direct cross-sheet range, and check that the name exists in Formulas > Name Manager.
The dropdown is empty or incomplete: Inspect the name’s Refers to range. It may stop before the newest entry, point to the wrong sheet, or include blank cells. Check that the source cells actually contain values.
The arrow is missing: Select a validated cell and check Data > Data Validation to confirm Allow is List and In-cell dropdown is enabled. The arrow is generally shown when the cell is active rather than permanently.
Some users see a different list: Check for sheet-scoped names with the same spelling, and confirm that everyone is opening the same saved workbook version. A workbook-level name avoids many scope conflicts.
Invalid text still appears: Review the Error Alert tab and use the Stop style if you want Excel to block ordinary typed entries. Remember that paste operations and other methods can bypass validation, so inspect imported or pasted data too.

When changing a source name or moving its cells, test a few destination cells afterward. Data Validation rules can be copied from other cells, and a copied rule may carry an unintended source or other settings. The Data Validation dialog is the quickest way to inspect the rule attached to a particular cell.

Excel for Mac and Excel for the web

In Excel for Mac, the core method is the same: define a workbook-level name for the source range, then create a List validation rule that uses that name. Ribbon placement and dialog layout can vary slightly by release, but look under Formulas for name tools and under Data for Data Validation. If the workbook will be shared with Windows users, save it as an Excel workbook and test the dropdown after reopening it.

Excel for the web has Data Validation features, but the available controls for defining or managing names can differ from the Windows desktop app. If you cannot create or edit the named range in the browser, set it up in desktop Excel first, save the workbook, then open it in the browser and verify the dropdown. A named range is also preferable to relying on a direct cross-sheet source because it is easier to manage across worksheets and app versions.

Frequently Asked Questions

Can I use a dropdown list from another sheet without a named range?
For a standard Data Validation list, a direct reference such as =Lists!$A$2:$A$10 is generally not accepted as the source. Define a range name that points to those cells, then use the name in the Source box.

Can I use a named range for more than one dropdown?
Yes. A workbook-scoped name can be used as the source for validation rules on multiple sheets or ranges. Each destination range can use the same name, and changes to the source list are reflected wherever the name points.

How do I add a new choice to the dropdown?
If the name points to a fixed range, add the choice inside that range or update the name in Formulas > Name Manager. If you used a table-column reference, add the choice as a new table row and check that the name still refers to the intended column.

Can the dropdown list update automatically when I add items?
Yes, if its named source is dynamic, for example a table column or a supported spill range. A name that points to a fixed block such as A2:A10 will not expand simply because you type in A11; extend that name or use a dynamic source.

Why can I type something that is not in the list?
The Error Alert may be disabled or set to a warning or information style rather than Stop. Also, pasting or filling cells can bypass validation. Check the rule and audit data that has been pasted or imported.

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.