'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix INDIRECT Data Validation Dropdown Errors in Excel for the Web
Question details
The user needs to fix an issue where an INDIRECT-based data validation dropdown formula produces an error when created in Excel for the Web.
- Product
- Excel for the Web
- Device & OS
- not provided
- Scenario
- Creating or editing a dependent dropdown list using the INDIRECT function directly in a web browser spreadsheet.
- Observed behavior
- The INDIRECT data validation rule returns an error when set up on the web version, even though the function works correctly when the rule is created in the desktop version of Excel.
Verify that your web browser is up to date and try refreshing your session, as temporary connectivity glitches can sometimes cause Excel for the Web to fail when parsing complex dynamic validation rules.
Recreate the Validation in a New Workbook or Copy from Desktop
Since the web version sometimes struggles with creating new complex validation rules in existing files, testing in a fresh file or applying the rule via the desktop app can bypass the web-specific bug.
Excel for the Web fully supports the INDIRECT function, but it occasionally fails to validate new rules created directly in the browser due to workbook-specific caching or hidden defined names.
Open a completely new workbook in Excel for the Web and set up a simple data validation rule using =INDIRECT($A$2) to see if the issue is isolated to your original file.
If you have the desktop version of Excel, open your file there, select the target cell, go to Data > Data Validation, and input your INDIRECT formula.
Save the file and reopen it in Excel for the Web. Copy the cell that already has the working validation and paste it to other required areas; the validation will carry over and function normally.
Use Defined Names with R1C1 References
Using a Named Range with an indirect reference can sometimes resolve inconsistencies when Excel for the Web attempts to parse dependent validation sources.
Experience Seamless Data Validation with WPS Office
If Excel for the Web continues to give you errors with complex formulas like INDIRECT, try WPS Office. It provides a robust, highly compatible desktop and web spreadsheet experience for free, ensuring your dropdown lists and formulas work without unexpected web glitches.
- 1. Download WPS Office: Visit the official WPS website and download the free desktop application for your operating system.
- 2. Open your Excel workbook: Launch WPS Spreadsheet and open your existing .xlsx file containing the dropdown lists.
- 3. Apply Data Validation: Go to the Data tab, select Validation, and seamlessly input your INDIRECT formulas without web-based restrictions.
Frequently Asked Questions
Does Excel for the Web support the INDIRECT function?
Yes, Excel for the Web supports the INDIRECT function. However, certain complex setups, especially within Data Validation rules created directly in the browser, may encounter inconsistent behavior or parsing errors.
Why does my dependent dropdown work on desktop but not online?
This typically occurs due to limitations or bugs in the web app's handling of specific named ranges or relative formula references during the creation phase. Creating the rule on desktop and opening it online often bypasses this validation block.
How can I make dynamic dependent dropdowns without INDIRECT?
You can use modern dynamic array functions like FILTER or XLOOKUP combined with Data Validation in newer versions of Excel. These functions are often more robust and process more reliably on the web than older INDIRECT methods.
