'%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)
Fix Excel INDIRECT Formula Referencing Only the First Dropdown Row
Question details
The user's dependent dropdown list built with the INDIRECT function is stuck referencing the first row's selector when applied to multiple rows.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Applying a dependent dropdown list utilizing the INDIRECT function across multiple rows.
- Observed behavior
- The dropdown works correctly for the first row, but fails in subsequent rows because the formula continues to reference the absolute initial cell instead of updating relative to each new row.
Before troubleshooting the formula, ensure that your named ranges perfectly match the exact text of the items in your primary dropdown list, as any spelling discrepancy will cause the INDIRECT function to fail.
Use Relative References in the Excel Desktop App
Adjust your INDIRECT formula to use relative references by removing the dollar signs, and ensure you are using the desktop version of Excel to avoid web limitations.
When you drag a data validation formula down a column, absolute references (like $C$2) lock the formula to that specific cell. Changing it to a relative reference (like C2) allows the row number to adjust dynamically.
Additionally, advanced data validation functions can sometimes behave unpredictably or lack support in Excel for the Web, making the desktop application necessary for complex dropdowns.
If you are using Excel for the Web, click 'Open in Desktop App' from the ribbon to ensure full functionality for data validation formulas.
Highlight the entire range of cells in the column where you want the dependent dropdown list to appear.
Navigate to the 'Data' tab on the ribbon and click on 'Data Validation' in the Data Tools group.
In the Source field, remove the dollar signs from your formula. For example, change =INDIRECT($C$2) to =INDIRECT(C2).
Click 'OK' to save the changes. Test the dropdowns in the subsequent rows to verify they are now referencing their corresponding primary cells.
Create Dynamic Dependent Dropdowns Easily with WPS Office
WPS Spreadsheet offers robust support for complex formulas, including INDIRECT, named ranges, and data validation. You can easily create dynamic dependent dropdown lists that work seamlessly across multiple rows without worrying about web-version limitations.
- 1. Define Named Ranges: Open your spreadsheet in WPS Office, select your source lists, and define your named ranges under the Formulas tab.
- 2. Access Data Validation: Select the target cells for your dependent dropdowns and navigate to Data > Validation.
- 3. Enter INDIRECT Formula: Choose 'List' from the Allow menu and enter your relative formula, such as =INDIRECT(C2), ensuring there are no absolute dollar signs.
- 4. Apply to Multiple Rows: Click OK and drag the cell's fill handle downward to seamlessly apply the dependent dropdown to all other rows.
Frequently Asked Questions
Why does my INDIRECT formula give an error when I remove the dollar signs?
You might encounter an error warning when setting up the data validation if the referenced cell (e.g., C2) is currently empty. You can safely click 'Yes' to continue. The dropdown will function correctly once the source cell is populated with a valid selection.
Why doesn't my dependent dropdown work in Excel for the Web?
Excel for the Web has certain limitations regarding advanced data validation and dynamic named ranges compared to the desktop version. Opening the file in the desktop application usually resolves these compatibility issues and restores full functionality.
How do I ensure my named ranges work with the INDIRECT function?
The named range must exactly match the text in your primary dropdown list. Since named ranges cannot contain spaces, if your primary dropdown text has spaces (e.g., 'Fruit List'), you must use underscores in the named range ('Fruit_List') and utilize the SUBSTITUTE function within your INDIRECT formula to map them correctly.
