Data Validation Rules Builder

---
name: data-validation-rules-builder
description: Use when adding data validation rules to a spreadsheet — dropdowns, ranges, regex, cross-cell checks, error messages. Triggers on "add data validation", "lock down this sheet", "validation rules", "input constraints".
---

# Data Validation Rules Builder

Add validation that catches bad data at entry, not at the month-end review. Most "shared sheet hygiene" problems are validation problems — typos in categories, empty required fields, dates in the wrong year. Block them at the cell.

## Required inputs

1. **The sheet** — existing file or schema for a new one
2. **The columns** to validate — most sheets only need 5-10 columns gated, not all of them
3. **For each validated column**: rule type (list / range / regex / date / formula), allowed values or pattern, required vs optional, custom error message
4. **Lookup lists** — where do dropdown values come from? An inline list, another tab, a named range?
5. **Cross-cell rules** — e.g., "Close Date > Created Date", "if Stage = Closed Won, Amount must be > 0"

If the user says "validate everything," push back — over-validation makes sheets unusable. Identify the 5-10 columns where bad data actually breaks downstream use.

## Approach

**openpyxl** has a `DataValidation` class — supports list, decimal, integer, date, time, textLength, custom (formula). Custom formula type is the workhorse for regex-style and cross-cell checks. Excel doesn't natively support regex in data validation, but you can approximate with combinations of `ISNUMBER(SEARCH(...))` or `COUNTIF(range, "*pattern*")`.

For Google Sheets via **gspread**, use `worksheet.set_data_validation` (or batch update with `addProtectedRange`-style requests). Sheets supports custom formulas and regex via `REGEXMATCH`.

For true regex in Excel, you need either modern Excel `REGEX` (Microsoft 365) or VBA — flag this constraint to the user upfront.

## Workbook structure

1. **Lists** (or **Validation_Lists**) — named ranges for every dropdown source. One column per list. `StatusList`, `OwnerList`, `CategoryList`. Centralized so updating a list updates every dropdown.
2. **Validation_Rules** — a documentation tab. One row per validated column: column name, rule type, allowed values / pattern, required, error message, last updated. So future maintainers know what's enforced.
3. **The actual data sheet(s)** — with validations applied to the column ranges (not just one cell — apply to the whole column or a generous range).
4. **Errors** (optional) — a separate tab using formulas to surface any rows currently failing a soft validation. Useful when you can't block entry but want a punch-list.

## Validation rule types

### List (dropdown)
- Source: a named range from the Lists tab.
- Show in-cell dropdown.
- Style: STOP (rejects invalid entries) for hard categories, WARNING for soft ones.

### Number range
- `Amount > 0`, `Discount BETWEEN 0 AND 100`.
- Set the error message to the actual constraint: "Amount must be greater than zero" — not "Invalid input."

### Date range
- `Close Date >= TODAY()` for forward-looking, `Created Date <= TODAY()` for past-only.
- For "current quarter only", use a formula validation referencing a named cell with the quarter boundaries.

### Text pattern (email, phone, ID)
- Excel: `=AND(ISNUMBER(SEARCH("@", A2)), ISNUMBER(SEARCH(".", A2)), LEN(A2)>5)` — approximate email check.
- Sheets: `=REGEXMATCH(A2, "^[^@]+@[^@]+\.[^@]+$")` — proper regex.
- For phone: strip and check length, or REGEXMATCH `^\+?[0-9 ()\-]{7,20}$`.

### Cross-cell consistency
- `=B2 > A2` for Close Date > Created Date.
- `=IF(C2="Closed Won", D2>0, TRUE)` for "if stage is closed won, amount > 0".
- Apply to the cell that should fail when the rule is violated (usually the dependent cell, e.g., Close Date).

### Required field
- `=LEN(TRIM(A2))>0`.
- Combine with conditional formatting (red fill on empty required cells) since data validation can't force a cell to be filled.

## Error messages

- Be specific. "Invalid value" tells the user nothing.
- Tell them HOW to fix it: "Stage must be one of: New, Qualified, Proposal, Closed Won, Closed Lost. Select from dropdown."
- Title bar: short. Body: the rule. Style: STOP for hard rules, INFORMATION for soft.

## Common pitfalls

- **Applying validation to one cell, not the column.** Insert a row, validation doesn't propagate. Apply to a generous range (rows 2:10000) or convert to a structured table where validations propagate automatically.
- **Hardcoded list in the validation source.** Edit the dropdown values, you have to edit the validation. Use named ranges from a Lists tab.
- **No error message customization.** "The value you entered is not valid" — useless. Always set custom title and body.
- **STOP everywhere.** Aggressive validation makes the sheet unusable for legitimate edge cases. Use WARNING/INFORMATION when the rule is "usually true."
- **Regex assumed in Excel without checking version.** Pre-Microsoft-365 doesn't have REGEX functions. Test the workbook target version.
- **Cross-cell validation that breaks on row insert.** If formulas reference `A2`, inserting at row 2 shifts but new row 2 may not have validation. Apply to the column.
- **No documentation of what's validated.** Six months later, someone wonders why their entry was rejected and can't find the rule. Validation_Rules tab.

## Common formula validations

- Email format: `=AND(ISNUMBER(SEARCH("@", X2)), ISNUMBER(SEARCH(".", MID(X2, SEARCH("@", X2), 100))))`
- URL starts with https://: `=LEFT(X2, 8)="https://"`
- Whole-number-only: `=X2=INT(X2)`
- Member of a long list: `=COUNTIF(MasterList, X2)>0`
- Unique per column (no dupes): `=COUNTIF(X:X, X2)=1`

## Output

Apply the validations and produce a Validation_Rules tab listing every rule. Print a summary: columns validated, rule types used, any rules that required Microsoft 365 or Sheets-only functions (so the user knows compatibility constraints). Recommend setting up a periodic check on the Errors tab if any soft validations were used.