Function Problems

Fix Excel Data Validation Drop-Down List Fails When Source Workbook is Closed

Tauseeq Magsi Oct 15, 2026 869 views

Question details

The user needs to maintain functional data validation drop-down lists when the external source workbook containing the list data is closed.

Fix Excel Data Validation Drop-Down List Failing When Source Workbook is Closed

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating an Excel data validation drop-down list that references a source range located in a different, external workbook.
Observed behavior: The drop-down list functions correctly when the external source workbook is open, but fails without displaying an error message as soon as the source workbook is closed.

Before you start

Ensure you have access to both the destination workbook and the external source workbook, and verify that the external data is up to date before setting up local references.

Solution 1Recommended

Import Source Data to a Local Hidden Worksheet

The most reliable way to maintain a functional drop-down list is to copy or link the external data into a hidden worksheet within the same destination workbook.

Modern versions of Excel generally cannot reliably process direct external workbook references in data validation when the source file is closed.

By moving the data locally and hiding the sheet, you ensure the drop-down list always has access to the data without cluttering your main workspace.

Open Both Workbooks

Open the destination workbook where you want the drop-down list, and the external source workbook containing the data.

Create a Reference Sheet

In your destination workbook, click the '+' icon at the bottom to add a new worksheet. Name it 'SourceData' or something similar.

Link the Data

In the new sheet, type a formula (e.g., ='[SourceWorkbook.xlsx]Sheet1'!A1) and drag it down to pull in the list from the external workbook, or simply copy and paste the values.

Apply Data Validation

Select the cells for your drop-down list, go to the Data tab, click Data Validation, choose 'List', and select the range in your new 'SourceData' sheet.

Hide the Worksheet

Right-click the 'SourceData' worksheet tab at the bottom of the screen and select 'Hide' to keep your workbook clean.

Import Source Data to a Local Hidden Worksheet

Reliable Functionality: Because the source data is now stored locally, the drop-down list will work regardless of whether the original external file is open or closed.

Solution 2

Use Power Query to Retrieve External Data

Power Query allows you to maintain a live connection to the closed external workbook, automatically refreshing the local list when the external data changes.

Create Reliable Drop-Down Lists with WPS Spreadsheet

WPS Office offers a powerful, free Spreadsheet tool that makes managing local data validation and cross-sheet references seamless. By keeping your data lists organized in local tabs, you ensure your drop-downs never break.

1. Create a Data Sheet: Open WPS Spreadsheet, click the '+' button to add a new worksheet, and dedicate it entirely to your list data.
2. Input List Values: Type or paste the values you want to appear in your drop-down menu into a single column.
3. Select Target Cells: Navigate back to your main worksheet and highlight the cells where you want the drop-down lists to appear.
4. Apply Validation: Go to the Data tab on the top ribbon, click Validation, select 'List' from the Allow drop-down, and highlight the range in your dedicated Data sheet.
5. Confirm and Hide: Click OK to apply the drop-down menu. You can then right-click your Data sheet tab and select 'Hide' to keep it out of view.

Free and lightweight alternative to Microsoft ExcelFully compatible with Microsoft Excel formats (.xlsx, .xls)Intuitive Data Validation tools with robust local referencingUnique tabbed interface for managing multiple workbooks effortlessly

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel drop-down list disappear when I close the source file?

Excel's data validation feature typically requires active memory access to the source range. When the external workbook is closed, the connection is suspended, causing the drop-down list to silently fail.

Can I use a named range to link to a closed workbook?

Historically, named ranges could sometimes bypass this restriction in older versions of Excel. However, in modern Excel versions (like Microsoft 365), referencing a closed workbook directly via named ranges for data validation is no longer reliably supported.

How do I hide the local source sheet so users don't see it?

Right-click the worksheet tab at the bottom of the screen that contains your source data and select 'Hide'. You can still reference this hidden sheet in your data validation settings without it cluttering your view.

Excel & Data Problems