Function Problems

How to Fix Excel Data Validation Lists Referencing Another Sheet

WPS Content Manager Oct 9, 2026 870 views

Question details

The user is unable to create a data validation dropdown list because referencing a data range on a different worksheet results in an error.

How to Fix Excel Data Validation Lists Using a Range on Another Sheet

Product: Excel
Device & OS: not provided
Scenario: Setting up a dropdown list in one worksheet where the allowed values (source list) are located on a separate worksheet within the same workbook.
Observed behavior: Excel blocks the action and displays an error stating that reference operators, array constants, or LAMBDA functions cannot be used for the validation criteria.

Before you start

Verify that the worksheet containing your source data is in the same workbook, and check that the sheet name does not contain special characters that might require single quotes in the formula.

Solution 1

Use Direct Cross-Sheet Referencing

Manually selecting the source range while the Data Validation dialog is open can ensure Excel formats the cross-sheet reference correctly.

In modern versions of Excel, you can directly reference another worksheet for a data validation list without writing the formula manually. This helps avoid syntax errors.

Open Data Validation

Select the cell or cells where you want the dropdown list. Go to the Data tab on the ribbon and click on Data Validation.

Select List Criteria

In the Settings tab of the Data Validation dialog box, select 'List' from the Allow dropdown menu.

Select Source Range

Click inside the Source box. Without closing the dialog, click on the tab for the other worksheet containing your data, highlight the required range (e.g., A1:A10), and click OK.

Solution 2Recommended

Create and Use a Named Range

Defining a named range for your source data is the most reliable way to bypass cross-sheet reference errors and makes managing dropdowns easier.

WPS Spreadsheet Solution

Easily Create Data Validation Lists with WPS Spreadsheet

WPS Spreadsheet provides a seamless and user-friendly experience for creating cross-sheet dropdown lists, effortlessly handling cell references without complex errors.

1. Open Your Workbook: Launch WPS Spreadsheet and open the workbook where you want to add the dropdown list.
2. Select Target Cells: Highlight the specific cell or range of cells where the data validation list should appear.
3. Access Data Validation: Navigate to the Data tab on the top ribbon and click the Validation button.
4. Configure List Source: Select 'List' under the Allow dropdown. Click the Source input field, switch to your secondary sheet, and highlight the data range.
5. Apply Settings: Click OK. WPS Spreadsheet will automatically format the cross-sheet reference correctly, completing your dropdown list.

Fully compatible with Microsoft Excel formats (.xls, .xlsx)Intuitive Data Validation interface that handles cross-sheet references smoothlyFree, lightweight, and fast office suite for all your daily needs

microsoft office alternative - wps office

Frequently Asked Questions

Can I use a data validation list source from a completely different workbook?

No, standard data validation requires the source data to reside within the same workbook. If you need data from another file, you must copy or link that data into a new worksheet within your current workbook first.

Why do I get an error when typing the sheet name manually in the Source box?

If your worksheet name contains spaces or special characters, Excel requires the sheet name to be enclosed in single quotes. For example, instead of =Sheet Name!$A$1:$A$10, you must type ='Sheet Name'!$A$1:$A$10.

How can I make my data validation list update automatically when I add new items?

To make a dropdown list dynamic, convert your source data range into a Table (Ctrl+T) before creating a Named Range from it. Alternatively, use the OFFSET function inside the Name Manager to define a dynamic range.

Excel & Data Problems