Arte SheetsLearn. Try. Understand.

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). Data validation can be bypassed by paste, import or programmatic edits; enforce real uniqueness and master-list membership in the receiving system too. COUNTIF criteria also treat *, ? and ~ as wildcards unless escaped.

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:B6COUNTIF in Assigned (Unique = 1)COUNTIF in Master E2:E5 (> 0)Combined Validation Flag
"TAG-101" (Assigned Once, In Master)111 (Valid Assignment)
"TAG-102" (Entered Twice in B3 & B5)2 (Duplicate)10 (Rejected — Duplicate)
"TAG-999" (Once, Not in Master List)10 (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.

Further reading