Data Validation & Auditing
Data Validation Formula: Enforce Unique IDs & Approved Master-List Membership
Prevent duplicate badge/PO numbers AND rogue unapproved codes simultaneously using AND(COUNTIF($B$2:$B$6, B2) = 1, COUNTIF($E$2:$E$5, B2) > 0).
Enforcing foreign-key integrity + primary-key uniqueness in a single spreadsheet rule!
Asset-assignment and purchase-order logs routinely break on two entry errors: assigning the SAME asset tag twice (duplicate primary key) or typing an asset tag that doesn’t exist in the IT hardware register (broken foreign key)!
Combining =AND(B2 <> "", COUNTIF($B$2:$B$6, B2) = 1, COUNTIF($E$2:$E$5, B2) > 0) enforces both relational database constraints at once: COUNTIF($B$2:$B$6, B2) = 1 rejects any duplicate assignment (both occurrences turn 0!), while COUNTIF($E$2:$E$5, B2) > 0 rejects any tag absent from Approved_Master E2:E5!
| Assigned Tag Entry in B2:B6 | COUNTIF in Assigned (Unique = 1) | COUNTIF in Master E2:E5 (> 0) | Combined Validation Flag |
|---|---|---|---|
| "TAG-101" (Assigned Once, In Master) | 1 | 1 | 1 (Valid Assignment) |
| "TAG-102" (Entered Twice in B3 & B5!) | 2 (Duplicate!) | 1 | 0 (Rejected — Duplicate) |
| "TAG-999" (Once, Not in Master List) | 1 | 0 (Unapproved!) | 0 (Rejected — Unknown Tag) |
Count Fully Validated Unique & Master-Approved Assignments
Sums per-row relational validation flags C2:C6 = --AND(B2 <> "", COUNTIF($B$2:$B$6, B2) = 1, COUNTIF($E$2:$E$5, B2) > 0).
=SUM(C2:C6)Single-Cell Validation Status Audit for Row 2 Entry B2
Diagnoses whether B2 is Valid, a Duplicate Assignment, or Absent from Approved Master E2:E5.
=IF(B2 = "", "Blank Entry", IF(COUNTIF($E$2:$E$5, B2) = 0, "Not in Master List", IF(COUNTIF($B$2:$B$6, B2) > 1, "Duplicate Entry", "Valid Unique Master ID")))Try the example
Use the original values or change them to test your understanding.