Excel Data Validation From Another Workbook: Best Methods

Yes, but the dependable approach is to bring or refresh the list in the workbook that needs the dropdown, then use a local named range as the data-validation source. Excel’s Data Validation list does not reliably use a direct reference to a range in another workbook, especially when that source workbook is closed.

Why a direct link is unreliable

Data validation can create a dropdown from values typed into its Source box, or from a range in the same workbook. A source on another worksheet in the same workbook can be used through a defined name. A range in a separate workbook is different: external references in validation lists are restricted, and a link that appears to work while the source file is open may fail when it is closed, moved, renamed, or unavailable.

One workaround sometimes suggested online is to use INDIRECT with an external workbook reference. INDIRECT requires the referenced workbook to be open to resolve an external reference, so this is not a dependable solution for a workbook that must work independently. It can also make a file harder to maintain because the path and workbook name are embedded in the reference.

The practical rule is simple: keep the values used by the dropdown inside the workbook containing the validation cells. The list can be copied there, pasted there when needed, or imported and refreshed. The dropdown then points to a local range, so it does not depend on another file being open.

Set up a local list and named range

For a short list that changes rarely, a hidden or clearly labeled worksheet is often enough. For example, suppose a workbook has an entry sheet where staff choose a department, and the authoritative department list is in a separate file. Copy the department values into a worksheet named Lists in the entry workbook, with a heading in A1 and the values in A2:A20.

In the destination workbook, add a worksheet for the copied values. Keep the list in one column, with a heading above it, and avoid blank cells within the actual entries.
Select the cells containing the values, such as Lists!A2:A20. Choose Formulas > Define Name, enter a name such as DepartmentList, and confirm that the name refers to the intended cells. Names cannot contain spaces.
Select the cells where users should make a selection. Choose Data > Data Validation. On the Settings tab, set Allow to List.
In Source, enter =DepartmentList, leave In-cell dropdown selected, and choose OK. Test the dropdown in one of the intended cells.

The named range keeps the validation rule readable and avoids entering a long range address in the Source box. It also makes it easier to move the list to another sheet later: update the name’s reference through Formulas > Name Manager rather than editing every validation rule.

Keep the local list current with Power Query

If the source workbook changes regularly, Power Query is a useful no-macro way to import its list into the destination workbook. It creates a local copy of the data that can be refreshed; it does not make the dropdown read live from the other file each time a user opens it. The source workbook must be reachable when you refresh.

In the destination workbook, choose Data > Get Data > From File > From Excel Workbook, then select the source file.
In Navigator, select the worksheet or, preferably, the named Excel table containing the values. Choose Load, or choose Transform Data first if you need to remove headings, blank rows, or unrelated columns.
Load the imported list to a worksheet in the destination workbook. Give the resulting table and its relevant column clear names, and create a workbook-level defined name for that column if needed.
Set Data Validation to List and use the local defined name as the Source. When the source list changes, choose Data > Refresh All, then check that the imported table and dropdown contain the expected entries.

Using a named table in the source workbook is generally easier to maintain than importing a fixed block of cells: the table can expand as new entries are added. After import, confirm that the destination table includes those new rows following a refresh. If the query is set to load to a worksheet, keep the validation name aimed at the imported table’s list column rather than at a temporary range that could be displaced by other content.

Power Query menu labels and available connectors can vary somewhat by Excel build and organizational settings. In Excel for the web, the full set of Power Query authoring and refresh capabilities may not match the Windows desktop app. If the workbook is shared online, verify that the intended users can refresh the query in their version of Excel and have access to the source file.

When copying the list is the better choice

A manual copy is often the clearest solution when a list has only a few values or changes infrequently. It avoids query setup, refresh permissions, and broken file paths. Add a note beside the copied values, such as “Update from the approved department list,” so another workbook owner knows where the values came from and how to maintain them.

For a one-time transfer, open both workbooks, copy only the values, and paste them into the destination workbook’s list sheet. Pasting values avoids importing formulas or formatting that the dropdown does not need. Then create or update the local defined name and test the validation cells. If the source values are copied from a filtered table, check that the pasted list contains all intended entries rather than only the visible rows.

Manual copying trades automation for control. It is a poor fit when the source changes daily or when multiple people maintain copies of the same list. In those cases, an import-and-refresh process is less likely to leave the dropdown stale, provided that someone owns the refresh process and the source remains accessible.

Make the dropdown grow as the list grows

A defined name tied to a fixed range such as A2:A20 will not include a new entry placed in A21. This is a common reason a newly added item fails to appear. Avoid selecting an enormous column without considering blank entries: extra blank cells can produce empty choices or make the list confusing.

For a maintained local list, format the values as an Excel table with Insert > Table or Home > Format as Table. Give the table and column meaningful names. A defined name can refer to the table’s list column, and the validation Source can refer to that defined name. This separates the validation rule from the table’s location and lets the table expand when entries are added at its bottom. Test the setup after adding a value, because a name accidentally aimed at a fixed cell range will not expand just because the cells are formatted as a table.

Keep the list values clean. Remove duplicates if repeated choices are not useful, avoid blank rows in the middle, and use consistent spelling and spacing. If people type directly into the list, protect or restrict that worksheet as appropriate; otherwise an accidental edit can change the choices available to everyone.

What happens to existing selections

Changing the source list does not automatically repair values already entered in the validated cells. If an item is removed from the list, existing cells may still display that value, even though it is no longer an available dropdown choice. The validation rule is primarily a check applied when users enter or change a value; changing the rule or source does not necessarily revisit every old entry.

After a list update, review existing data if removed options matter. Use Data > Data Validation and the Circle Invalid Data command to identify entries that do not meet the current validation rule. The command is useful for review, but it does not decide how to replace outdated selections. A data owner should choose whether to update, retain, or clear each affected value.

Also distinguish a dropdown from a guarantee that all data is valid. Users may paste data into cells, and pasted content can bypass the normal entry experience. Validation settings can also be changed by someone with permission to edit the workbook. For important records, combine validation with review, protection, or other controls suited to the process.

Troubleshoot missing or stale choices

The dropdown is empty: Check that the name exists in Formulas > Name Manager, that it refers to cells containing values, and that the validation Source begins with an equals sign followed by the correct name.
A new value is missing: Check whether the defined name uses a fixed range that stops above the new row. Expand the range or use a table-based name, then test again.
The list works only while another file is open: The rule likely depends on an external reference or INDIRECT. Replace it with a local copy or a Power Query import and point validation at a local range.
Refresh fails: Check that the source file has not moved or been renamed, that the user has permission to access it, and that the query’s source path is still valid. Refresh the query before diagnosing the validation rule itself.
Some entries appear blank or duplicated: Inspect the imported or copied list for blank cells, spaces, duplicates, and values that look identical but differ in spelling. Clean the source list and refresh or recopy it.

When investigating, select a cell with the dropdown and open Data > Data Validation to inspect its Source. Compare that source with the name’s reference in Name Manager. This separates three common causes: the validation rule points to the wrong name, the name points to the wrong cells, or the local list itself has not been refreshed.

Choosing between the main methods

Use a manual copy for a small, stable list; use Power Query when the list changes and needs a repeatable refresh; and use VBA only when a specific workflow requires automation that the built-in import process cannot provide. A macro can copy or rebuild a list, but it introduces security prompts, maintenance, and the need for users to enable macros. It is also easier to make a workbook behave differently across environments. For many teams, a local list plus a clearly documented refresh step is simpler and safer.

Think about ownership as well as technology. Decide which workbook is authoritative, who updates it, how often the destination workbook should be refreshed, and what users should do if a choice is retired. If multiple workbooks need the same controlled list, keeping one authoritative source and importing it consistently is better than letting each workbook develop a different manually maintained version.

Frequently Asked Questions

Can Excel data validation use a list from another workbook?

Not reliably as a direct external range reference. A link may appear to work under limited conditions, but it can depend on the other workbook being open and available. The robust approach is to place or import the list into the workbook with the dropdown and use a local named range.

Does the source workbook have to be open?

It should not have to be open for a dropdown that points to a local list. If the dropdown depends on an external reference resolved through INDIRECT, the source workbook generally must be open for that reference to resolve. A Power Query refresh does require access to the source file when the refresh runs.

Will Power Query update the dropdown automatically?

No. Power Query imports data into the destination workbook, and the imported results change when the query refreshes. Configure and run a refresh, such as Data > Refresh All, then confirm that the validation name includes the refreshed rows.

Can I use a list on another sheet in the same workbook?

Yes. Put the list on another worksheet, define a name for its cells, and use that name in the validation Source. A named range is the usual way to avoid entering a direct cross-sheet range in the Source box.

Why do old entries remain after I remove an option?

Removing a choice from the source list does not automatically delete or replace values already entered in validated cells. Review existing entries after list changes, and use Data > Data Validation > Circle Invalid Data to help identify values that no longer satisfy the rule.

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.