How to Fix INDIRECT Formula Errors for Named Ranges with Spaces
Question details
The user needs to fix an issue where the INDIRECT formula fails to retrieve data because the referenced cell contains spaces, while the actual named range uses underscores.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Setting up dependent drop-down lists or dynamic data validation where user-selected values contain spaces, but the corresponding named ranges must use underscores due to syntax rules.
- Observed behavior
- The INDIRECT function returns a #REF! error because the text string with spaces does not exactly match the defined named range.
Verify that your named ranges have been successfully created using underscores in place of spaces (for example, 'Data_Set' instead of 'Data Set'), and locate the exact cell reference of your drop-down menu.
Use the SUBSTITUTE Function Inside INDIRECT
Wrap the cell reference with a SUBSTITUTE formula to automatically replace spaces with underscores before INDIRECT evaluates the text string.
Since named ranges cannot contain spaces, standard practice is to use underscores. By nesting the SUBSTITUTE function inside INDIRECT, the formula automatically translates spaces from your drop-down list into underscores, creating a perfect match with your named range.
Click on the cell where you want to display the retrieved data or set up your dependent data validation list.
Type the formula =INDIRECT(SUBSTITUTE(J18, " ", "_")). Replace 'J18' with the actual cell reference that contains the text with spaces.
Press Enter to apply the formula. Change the value in your drop-down menu to verify that the formula successfully pulls data from the correct named range without returning a #REF! error.
Modify Drop-Down Values to Match Named Ranges
Change your source data so that the drop-down list natively displays underscores, matching the named range exactly without requiring extra formulas.
Master Complex Formulas Easily with WPS Spreadsheet
WPS Spreadsheet makes it effortless to manage named ranges, resolve complex INDIRECT scenarios, and build dynamic drop-down lists with highly intuitive formula assistants.
- 1. Download and install: Get WPS Office for free from the official website and install it on your device.
- 2. Open your spreadsheet: Launch WPS Spreadsheet and open your existing file containing the drop-down lists.
- 3. Access the Name Manager: Go to the Formulas tab and click on Name Manager to review your underscore-separated named ranges.
- 4. Apply the formula: Use the formula bar to type =INDIRECT(SUBSTITUTE()) and leverage the intelligent prompts to correctly map your ranges.
Frequently Asked Questions
Why do I get a #REF! error when using the INDIRECT function?
The #REF! error means that the text string evaluated by the INDIRECT function does not match any valid cell reference or defined named range. When working with drop-down lists, this is most commonly caused by having spaces in the drop-down selection while the named range uses underscores.
Can I include spaces when defining a named range in my spreadsheet?
No. Spreadsheet software rules prohibit the use of spaces and certain special characters in named ranges to avoid syntax conflicts within formulas. You must use underscores, periods, or camel case (e.g., SalesData) to separate words.
How do I handle drop-down values that contain multiple spaces?
The SUBSTITUTE function replaces all instances of a specified character by default. Using =INDIRECT(SUBSTITUTE(A1, " ", "_")) will replace every space in the cell with an underscore, so a three-word phrase like 'Very Highly Willing' correctly transforms into 'Very_Highly_Willing'.
Can I use SUBSTITUTE to replace characters other than spaces?
Yes. If your drop-down values contain dashes, ampersands, or other characters not allowed in named ranges, you can nest multiple SUBSTITUTE functions. For example, =INDIRECT(SUBSTITUTE(SUBSTITUTE(A1, "-", "_"), " ", "_")) will replace both dashes and spaces with underscores.
