How to Create Conditional Data Validation for a Four-Digit Excel Code
Question details
The user needs to set up a conditional data validation rule in column C that requires a four-digit whole number only if 'CHK' is selected in column B, while allowing any entry for other transaction types.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Setting up specific data entry rules for financial or transaction logs based on adjacent cell values.
- Observed behavior
- The goal is to restrict input in column C to whole numbers between 0 and 9999 when column B equals 'CHK', and display the numbers with leading zeros.
Ensure your transaction data is organized in adjacent columns (e.g., Column B for transaction type and Column C for the code) before applying the validation rule.
Use Custom Data Validation with an OR/AND Formula
Apply a custom formula in the Data Validation settings to enforce the four-digit rule conditionally based on the value in an adjacent column.
By combining the OR, AND, and MOD functions, you can create a logical test that checks the transaction type first, then validates the number format only if required.
Select the range in column C where you want the validation applied (e.g., C2:C100). Ensure that the top cell (C2) is the active cell in your selection.
Right-click the selection, choose 'Format Cells', go to the 'Custom' category, and enter '0000'. This ensures that numbers smaller than four digits (like '12') will display with leading zeros (like '0012').
Navigate to the 'Data' tab on the top ribbon and click 'Data Validation'.
Under the Settings tab, select 'Custom' from the Allow dropdown menu. Enter the following formula exactly: =OR(B2<>"CHK",AND(C2>=0,C2<=9999,MOD(C2,1)=0)).
Go to the Error Alert tab to add a custom message explaining the four-digit whole number requirement when 'CHK' is selected, then click OK to save your rule.
Apply Conditional Data Validation Seamlessly in WPS Spreadsheet
WPS Spreadsheet fully supports advanced data validation formulas, including complex logical checks with OR and AND functions. It provides a lightweight, fast, and completely compatible environment for your complex Excel workflows.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet containing the transaction data.
- 2. Access Data Validation: Highlight your target cells in column C, navigate to the 'Data' tab on the ribbon, and click 'Validation'.
- 3. Apply the Custom Formula: Choose 'Custom' from the Allow dropdown menu, paste your OR/AND formula, set your error alerts, and click OK to enforce the rule.
Frequently Asked Questions
Why isn't my conditional data validation formula working for the whole column?
If you apply the rule to an entire column, ensure your formula references the correct active cell (e.g., B2 and C2) without absolute dollar signs ($). Using absolute references like $B$2 will cause the validation to evaluate every row against cell B2 instead of checking its own respective row.
Will data validation automatically format my numbers with leading zeros?
No, data validation only restricts what can be typed into the cell; it does not change how the cell is displayed. You must separately apply a custom number format of '0000' to the cells so that numbers like '15' visually display as '0015'.
Can I add more conditions for different transaction types?
Yes. You can nest multiple IF statements or expand the OR/AND logic within the Custom Data Validation formula to accommodate additional transaction types with different data entry rules.
