Fix a Dependent Drop-Down List That Skips Values in WPS Office Excel
Question details
The user needs to correct a dependent Excel drop-down list that omits valid values because the named range used by INDIRECT starts at the wrong row.
- Product
- WPS Office Spreadsheets (Excel)
- Device & OS
- not provided
- Scenario
- Managing dependent drop-down lists that use INDIRECT and defined names in an Excel workbook.
- Observed behavior
- The dependent drop-down list skips valid options even though the source range contains many valid values.
Open the workbook that contains the drop-down lists and confirm you can access the Formulas tab and Name Manager. Note the intended source ranges for each dependent list before editing defined names.
Correct Defined Name Source Ranges in Name Manager for a Dependent Drop-Down List
Use this when a dependent drop-down list skips values because the defined name referenced by INDIRECT points to the wrong starting row or an incomplete range.
Excel dependent drop-down lists typically rely on INDIRECT with defined names. If a defined name starts at the wrong row, the drop-down only sees the smaller range and silently omits valid entries.
Click the Formulas tab in WPS Office Excel, then choose Name Manager in the Defined Names group.
In the Name Manager dialog, select the named range used by the INDIRECT function, such as Rost or Entschlacker.
Click Edit and change the Refers To box to the exact intended range, for example =Eingabe!$D$6:$D$65 or =Eingabe!$C$6:$C$10.
Repeat this for every named range used by INDIRECT, making sure no name starts at the wrong row or ends before the last valid value.
Click OK to save, then open the dependent drop-down list to verify that all expected values now appear.
Fix Dependent Drop-Down Lists in WPS Office Spreadsheets
Open Formulas > Name Manager in WPS Office Excel, correct each named range used by INDIRECT to point to the full source rows, and the dependent drop-down list will display all valid entries again.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office and open the Excel workbook that contains the dependent drop-down lists.
- 2. Go to Formulas > Name Manager: Click the Formulas tab, then click Name Manager to see all defined names in the workbook.
- 3. Edit each INDIRECT source range: Select each named range used by INDIRECT and set Refers To to the full intended range, such as =Eingabe!$D$6:$D$65 or =Eingabe!$C$6:$C$10.
- 4. Save and test the dependent list: Click OK to save the name changes, then click the dependent dropdown cell and confirm all valid values now appear.
Frequently Asked Questions
Why does my dependent drop-down list skip values even though the source has many valid options?
The named range used by INDIRECT likely starts at the wrong row or points to a smaller range. Open Formulas > Name Manager and verify that Refers To covers every intended source cell.
How do I open Name Manager in WPS Office Excel?
Open the workbook, click the Formulas tab, and select Name Manager from the Defined Names group. Then choose the named range you need to edit.
Can I use INDIRECT with named ranges that include blank cells?
Yes, but blank cells may appear as empty entries in the drop-down. It is better to define exact source ranges without extra blank rows and ensure the named range starts and ends at the correct row.
What should I check if the dependent list still skips values after editing the named range?
Check every defined name used by INDIRECT and the data validation formula. Also make sure the source values have no unintended leading or trailing spaces, duplicates, or blank rows.
Does this fix work when the workbook is opened in Microsoft Excel?
Yes. WPS Office Spreadsheets stores defined names and data validation in Excel-compatible formats, so the corrected ranges continue to work in Microsoft Excel.
