How to Fix INDIRECT Named Range Error in Excel Data Validation
Question details
The user needs to resolve an issue where a dependent drop-down list using the INDIRECT function returns a 'named range cannot be found' error.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Setting up conditional data validation to create a dependent drop-down list using the INDIRECT function and named ranges.
- Observed behavior
- Excel displays an error stating the named range cannot be found, even though the source value perfectly matches an existing named range.
Ensure that your defined named ranges do not contain any spaces or invalid characters, and that the text in your primary drop-down list perfectly matches the names of those ranges.
Use Correct INDIRECT Syntax with Spill Ranges
Fix validation limitations by defining a dynamic spill range and correctly wrapping your named range reference in quotes within the INDIRECT function.
Data validation sometimes struggles with calculated ranges but works reliably with spill ranges. By utilizing the spill reference operator (#), you can feed a dynamic array into your data validation list without encountering missing range errors.
Create your separate lists of items. For example, place U.S. cities in cells B1:B3 and German cities in cells C1:C2.
In an empty cell like A1, write an IF statement to determine the output array based on your primary selection cell (e.g., G4). For example: =IF(G4="USA",B1:B3,C1:C2).
Go to Formulas > Name Manager and click New. Name it 'listValidation' and in the 'Refers to' box, type =Sheet1!$A$1# (the hashtag is crucial as it targets the entire spill array).
Select the cell for your dependent drop-down list (e.g., G5). Go to Data > Data Validation, select 'List' under Allow, and enter =INDIRECT("listValidation") in the Source box.
Disable R1C1 Reference Style
Turn off the R1C1 reference style, which can conflict with the INDIRECT function and cause named ranges to be unrecognizable in data validation.
Easily Create Dependent Drop-Down Lists in WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like INDIRECT, Named Ranges, and dynamic arrays. You can seamlessly manage conditional data validation lists with an intuitive interface, completely avoiding obscure reference syntax errors.
- 1. Open Your Workbook: Launch WPS Spreadsheet and open the file where you want to set up your drop-down lists.
- 2. Define Your Ranges: Highlight your source data ranges, right-click, and select 'Define Name' to assign them easily identifiable names.
- 3. Access Data Validation: Select your target cell, go to the 'Data' tab on the top ribbon, and click on 'Validation'.
- 4. Configure the Drop-Down: In the Validation dialog, choose 'List' from the Allow menu and enter your formula, such as =INDIRECT(A1), in the Source field.
- 5. Confirm and Use: Click 'OK'. Your dependent drop-down list is instantly created and ready for use.
Frequently Asked Questions
Why does Data Validation say 'The source currently evaluates to an error'?
This warning occurs if the reference cell used inside the INDIRECT function is currently blank or contains text that doesn't exactly match an existing defined named range. You can click 'Yes' to accept it; the drop-down will function correctly once you populate the reference cell with valid data.
How do spaces in text affect the INDIRECT function?
Named ranges cannot contain spaces. If your primary drop-down list contains spaces (for example, 'New York'), INDIRECT will fail. You must define the named range with an underscore ('New_York') and use the SUBSTITUTE function in your validation source, like =INDIRECT(SUBSTITUTE(A1, " ", "_")).
Can I use the INDIRECT function with Excel Tables instead of named ranges?
Yes, but you must use precise structured referencing. For Data Validation, you typically need to wrap the table reference in the INDIRECT function using syntax like =INDIRECT("TableName[ColumnName]").
Why does my dependent drop-down list show up completely blank?
A blank drop-down usually indicates that the INDIRECT function evaluated successfully, but the named range it pointed to contains empty cells. Verify that your named ranges perfectly wrap the intended data without including excess blank rows.
