Contact
12 programs
Explore the site

WPS Spreadsheet · Data validation · Beginner

Validate Numeric and Date Inputs in WPS Spreadsheet

Use Data → Validation to constrain whole numbers and dates, then add a row-dependent Custom rule. Test accepted endpoints and rejected outside values; separately audit blanks, paste and dependency changes.

Editorial standards

Quick answer

Use Data → Validation to constrain whole numbers and dates, then add a row-dependent Custom rule. Test accepted endpoints and rejected outside values; separately audit blanks, paste and dependency changes.

English WPS Spreadsheets for Windows desktop; documentation-qualified basic local-workbook workflow, reviewed 20 October 2026. Installed build, region and free-edition execution are not verified; menu labels may differ. No web, mobile, macOS or Linux parity is claimed.

Build a small dispatch form that accepts whole-number quantities, dates inside a fixed window, and quantities constrained by another cell. Validation helps during entry; it is not a security boundary or a guarantee that pasted or previously entered data is clean.

Before you begin

Create the local workbook WPS-Validation-Final.xlsx and a sheet named Dispatch. Use Data > Validation, as documented in WPS-specific lessons and Windows help. Basic rules, messages and invalid-data circles are listed as Free in the Windows documentation. Actual labels and alert behavior need checking in your installation. Do not buy a template or activate an AI service for this exercise.

1. Lay out the form

Enter these cells individually so dates do not depend on ambiguous slash-formatted text:

Cell Entry
A1 Job
B1 Units
C1 Dispatch date
D1 Capacity
E1 Reserved
A2:A4 JOB-A, JOB-B, JOB-C respectively
D2:D4 8, 12, 5 respectively
G1 Start
G2 =DATE(2026,11,1)
H1 End
H2 =DATE(2026,11,30)

Format C2:C4 and G2:H2 as dates; confirm G2 is 1 November 2026 and H2 is 30 November 2026. If your locale uses semicolons between function arguments, use those separators in formulas. Keep the year, month and day arguments unchanged. Save before adding rules.

2. Restrict the units

  1. On Dispatch, select B2:B4 and open Data > Validation.
  2. In Settings choose Whole number, condition between, minimum 1, maximum 20.
  3. Configure an Input Message such as Enter a whole number from 1 to 20. Enable the Error Alert and select a blocking style such as Stop, when offered. Enter an error message explaining the same range. A warning that allows acceptance is not the blocking behavior wanted here.
  4. Confirm the rule. Type 1 in B2 and 20 in B3: both endpoints must be accepted. Try 0, 21 and 2.5 in B4 one at a time. Each must be rejected when typed with the blocking alert enabled. Cancel each invalid entry; do not override a warning.
  5. Finish with B2:B4 containing 1, 20, 6.

If your build does not offer the documented type or cannot block the invalid typed values, stop and investigate the rule rather than claiming that the form is protected.

3. Restrict dispatch dates

Select C2:C4, open Validation and choose Date, between. Use =$G$2 as the start and =$H$2 as the end if the fields accept cell-reference formulas. If they require literal dates, enter the same two dates in your locale’s accepted format and verify the interpreted boundaries in the dialog. Configure a blocking Error Alert.

Enter =DATE(2026,11,1) in C2 and =DATE(2026,11,30) in C3; both are within the inclusive window. Try =DATE(2026,10,31) and =DATE(2026,12,1) in C4 separately; the required behavior is rejection. Cancel the rejected edits and finish C4 with =DATE(2026,11,15). Reopen C4’s rule to confirm it covers the same dates as C2. Formula-entry checks supplement, rather than replace, testing ordinary typed dates in your local format.

4. Add a row-dependent custom rule

Select E2:E4 with E2 active. Open Validation, choose Custom, and enter:

=AND(ISNUMBER(E2),E2>=0,E2<=$D2)

This rule allows a numeric reservation between zero and that row’s capacity. It allows decimals deliberately; the whole-number rule belongs to B. E2 moves with the target row, while $D2 keeps the capacity column fixed. Configure a blocking error alert, explaining that the reservation cannot exceed the row’s capacity. Clear Ignore blank if available, but do not treat that checkbox as proof that all empty cells will be prohibited.

Test E2 with 0, then 8 (accepted), and 9 (rejected). Test E3 with 12 (accepted) and 13 (rejected). These different limits catch an accidentally locked $D$2. Finish E2:E4 with 8, 12, 5. In G4 enter =SUM(E2:E4), expecting 25.

5. Audit, save and reopen the main workbook

Inspect B2:B4 (1, 20, 6), C2:C4 (1, 30 and 15 November 2026), E2:E4 (8, 12, 5) and G4 (25). Use Data > Validation > Circle Invalid Data for a supporting review. No marked fixture cells should remain; still compare values directly because a missing or overwritten rule cannot validate anything.

Save WPS-Validation-Final.xlsx, close and reopen it. Reopen representative rules at B2, C2 and E2 without changing them. Confirm the bounds/formula, alert settings and saved values. Keep this final workbook intact.

6. Test bypasses only in a separate copy

Create WPS-Validation-Experiments.xlsx with Save As and confirm its name before editing Dispatch.

  • Clear E4 and observe whether a blank is retained. An initially blank or deleted cell may survive even with Ignore blank cleared. Review missing required entries separately; this rule does not prevent saving an incomplete form.
  • Put 99 in J2, copy it and paste into B2. Record whether the paste is rejected, inserts an invalid value, or replaces the destination rule. Reopen B2’s validation afterward. Do not assume that normal paste or Paste Special has the same behavior as typing.
  • Change D3 from 12 to 4 while E3 remains 12. The previously valid reservation is now inconsistent. Run Circle Invalid Data and inspect E3 directly. The change in a dependency need not produce the same immediate alert as editing E3 itself.

Do not save these experiments over the main file. To recover a rule, restore from the final workbook or reapply the original rule to the explicit range and retest the boundaries.

Troubleshooting and evidence

A rule on the wrong range, a disabled error alert or a warning-only style can explain accepted invalid input. Text that looks like a date is not necessarily a date value. A custom formula must be written relative to the first active cell; inspect row-specific behavior rather than merely the formula text. Do not copy the unquoted text example in the older WPS lesson: the numeric custom rule above avoids that ambiguity.

Official references

These are documentation-based instructions. The fixture expectations were checked independently; no native WPS run, application screenshot, installed-edition entitlement test or cross-program round trip is claimed. Academy page dates do not identify an application build.