Create Dependent Drop-Down Lists with Increasing Codes in WPS Spreadsheet
Question details
Create a dependent drop-down list in which the next row offers codes with the number increased from the code selected in the previous row.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The user wants to select a code such as ir001 or sr001 and have the following row offer the corresponding next-numbered options, such as ir002 and sr002.
- Observed behavior
- The desired result is an automatically progressing code sequence in dependent Data Validation drop-down lists.
Enter the first code in the starting row and keep every code in the same format: a two-letter prefix followed by a three-digit number. Reserve two nearby helper columns to calculate the available codes for each next row.
Use helper formulas as the source for each dependent drop-down list
Use this method when each next row should offer multiple prefixes while automatically increasing the numeric part of the code selected in the previous row.
Helper cells calculate the next available codes from the row above. Data Validation then uses those helper cells as the drop-down source, so the list updates whenever the preceding selection changes.
In the first row of your code column, such as B10, enter or select the starting code, for example ir001.
In a helper cell for the next row, such as D11, enter ="ir"&TEXT(VALUE(RIGHT(B10,3))+1,"000"). This returns ir002 when B10 contains ir001 or sr001.
In the adjacent helper cell, such as E11, enter ="sr"&TEXT(VALUE(RIGHT(B10,3))+1,"000"). This returns sr002 when B10 contains ir001 or sr001.
Select B11, go to Data > Data Validation, choose List, and set the Source to =$D11:$E11. Click OK to make ir002 and sr002 available in B11.
Copy D11:E11 and the Data Validation in B11 down to later rows. Each helper row should reference the code cell immediately above it, such as D12 using B11.
Use named ranges for a cleaner dependent list setup
Use named ranges when you want to keep helper formulas on a separate worksheet or make the Data Validation source easier to manage.
Create Automatically Increasing Code Drop-Downs in WPS Spreadsheet
WPS Spreadsheet lets you combine formulas and Data Validation lists to generate the next code number from the previous selection. Use helper cells for the calculated ir and sr options, then link the next cell's drop-down to those results.
- 1. Enter the starting code: Type the first code, such as ir001, into the first cell of the code column.
- 2. Add next-code formulas: In two helper cells, use formulas that add 1 to the three-digit suffix and return the ir and sr versions of the next code.
- 3. Set list validation: Select the next code cell and use Data > Data Validation > List to set the helper-cell range as the Source.
- 4. Fill formulas and validation down: Copy the helper formulas and validated cell down the column so each row uses the selection above it.
Frequently Asked Questions
How do I keep leading zeros when increasing a code number?
Wrap the increased number in TEXT with a three-digit format, such as TEXT(VALUE(RIGHT(B10,3))+1,"000"). This changes 001 to 002 without removing the leading zeros.
Can the next drop-down show both ir and sr codes regardless of the previous prefix?
Yes. Create one helper formula beginning with "ir" and another beginning with "sr", both using the previous row's numeric suffix. Set Data Validation > List Source to the two helper cells.
Where do I find Data Validation in WPS Spreadsheet?
Select the target cell, open the Data tab, and choose Data Validation. Choose List as the validation type, then specify the cells containing the generated code options.
Why does my dependent drop-down not update after I select a code?
Check that each helper formula references the code cell in the row directly above and that the Data Validation Source points to the helper cells for the current row. Also confirm the selected code ends with a valid three-digit number.
Can I use this setup in an Excel workbook?
Yes. WPS Spreadsheet can edit compatible .xlsx and .xls files. Data Validation lists and standard formulas such as LEFT, RIGHT, VALUE, and TEXT can be used for this setup.
