Function Problems

WPS Spreadsheet Conditional Data Validation for Drop-Downs and Six-Digit Codes

Emma Brown Nov 12, 2026 869 views

Question details

The user needs conditional data validation that accepts standard items from a drop-down list but permits a six-digit code when the selected item class is Other.

Product: WPS Spreadsheet
Device & OS: not provided
Scenario: A worksheet uses one field for a standard item selection or an alternate six-digit code, depending on the value selected in a class or type cell.
Observed behavior: The required validation rule depends on whether the user selects Other, and the exact source list and six-digit-code requirements have not been specified.

Before you start

Identify the cell containing the item class, the cell receiving the item or code, and the source range for standard items. Decide whether leading-zero codes such as 012345 are valid and whether six-digit codes must come from a restricted list.

Solution 1Recommended

Use a class cell and a custom validation rule for the item or code cell

Use this design when Other can accept any six-digit code and standard items should remain limited to a source list.

A single standard List validation cannot simultaneously show a fixed drop-down and allow arbitrary values outside that list. A dependable layout uses one cell for the class and a second cell for the standard item or six-digit code.

In this example, A2 contains the class, B2 contains the value, and ItemList is a named range containing the approved standard items. The formula also preserves leading-zero six-digit codes by validating the entry as text or a number.

Create the class selector

Select the class cells, open Data > Data Validation, choose List, and select the class source range that includes Other.

Add a standard-item list to the value cell

Select B2 or the target value range, open Data > Data Validation, choose List, and set the Source to =ItemList. This provides the normal drop-down for standard entries.

Replace the value rule with a custom formula

With the value range selected, open Data > Data Validation, choose Custom, and enter =IF($A2="Other",AND(LEN(B2)=6,IFERROR(ISNUMBER(--B2),FALSE)),COUNTIF(ItemList,B2)>0). Adjust A2 and B2 to match the first row of your selected range.

Configure the error message

On the Error Alert tab, keep Stop selected and enter a message such as Enter an approved item or a six-digit code when Other is selected. Click OK and test both branches.

Important limitation: Applying Custom validation replaces the visible List drop-down for that cell. If the same cell must always show a drop-down and also accept arbitrary six-digit values, use the restricted-list or macro-based approach instead.

Solution 2

Use a helper list when six-digit codes come from an approved range

Use this option when the six-digit codes are restricted to a known list and must appear in the same drop-down as standard items.

Build Conditional Drop-Down Validation in WPS Spreadsheet

WPS Spreadsheet lets you create class selectors, named source ranges, drop-down lists, and custom validation formulas in an Excel-compatible workbook. Use a separate class and value cell for flexible six-digit codes, or use dependent named ranges when every permitted value must remain in a drop-down.

1. Set up the source ranges: Open WPS Spreadsheet and place standard items, class values, and any approved six-digit codes in organized source ranges.
2. Create the class drop-down: Select the class cell, choose Data > Data Validation, select List, and specify the class source range.
3. Add the conditional rule: Select the value cell, choose Data > Data Validation > Custom, and enter a formula that checks for Other and validates exactly six digits.
4. Test and save the workbook: Try a standard item, an invalid value, and a six-digit code, then save the file as XLSX to preserve Microsoft Excel compatibility.

Create custom formulas that validate six-digit entries and standard list valuesUse dependent drop-down lists based on the selected item classOpen, edit, and save Microsoft Excel XLSX files with compatible data-validation rulesWork in a familiar, lightweight spreadsheet interface without rebuilding the workbook

microsoft office alternative - wps office

Frequently Asked Questions

Can one cell display a drop-down and also accept any six-digit code?

Not with ordinary List data validation alone. A List rule restricts entries to its source range. Use separate class and value cells with a Custom rule, or use a macro or approved combined list if both behaviors must occur in one cell.

What custom formula validates six digits when Other is selected?

If A2 contains the class and B2 contains the entry, use =IF($A2="Other",AND(LEN(B2)=6,IFERROR(ISNUMBER(--B2),FALSE)),COUNTIF(ItemList,B2)>0). Replace the cell references and ItemList with your worksheet's actual locations.

How can I allow codes that begin with zero?

Keep the entry cell formatted as Text or enter the code with a leading apostrophe, such as '012345. The LEN check confirms six characters while the numeric conversion confirms that all characters are digits.

How do I restrict Other to a specific list of six-digit codes?

Create a named range for the approved codes, such as OtherCodes, and use a dependent List validation rule with =IF($A2="Other",OtherCodes,StandardItems). This keeps the drop-down visible and blocks unapproved codes.

Excel & Data Problems