Data Validation & Hygiene
Dependent Dropdown Conditional List Formula (FILTER & Parent-Child Audit)
Generate a dynamic cascading list of valid child options for a selected parent category using FILTER and UNIQUE, and audit existing rows to catch mismatched parent-child pairs.
Replace brittle INDIRECT named ranges with a single normalized mapping table
Classic cascading dropdowns required creating a separate named range for every parent category and stripping spaces with SUBSTITUTE inside volatile INDIRECT. Maintaining dozens of named ranges breaks as soon as new departments or product lines are added.
Storing all valid (Department, Cost_Center) pairs in a two-column mapping table A2:B7 lets =UNIQUE(FILTER(B2:B7, A2:A7 = E2, "Invalid Department")) spill the exact child list for parent E2. Crucially, if a user later changes the parent Department in E2 without re-selecting Child Cost_Center F2, an explicit pair check SUMPRODUCT((A2:A7 = E2) * (B2:B7 = F2)) > 0 flags stale parent-child combinations immediately.
| Parent Department | Valid Cost Center | Selected Parent (E2) | Spilled Child Options |
|---|---|---|---|
| Engineering | CC-Cloud | Engineering | CC-Cloud |
| Engineering | CC-QA | Engineering | CC-QA |
| Engineering | CC-Security | Engineering | CC-Security |
| Finance | CC-Treasury | (Excluded) | — |
| Finance | CC-Audit | (Excluded) | — |
| Operations | CC-Logistics | (Excluded) | — |
Dynamic Conditional List Spill for Selected Parent Category
Extracts unique allowed child values from B2:B7 matching the parent category selected in E2.
=TEXTJOIN(", ", TRUE, UNIQUE(FILTER(B2:B7, A2:A7 = E2, "Invalid Department")))Stale Selection Integrity Check (Parent-Child Pair Validation)
Verifies that the chosen child option in F2 actually belongs to the active parent category in E2.
=IF(SUMPRODUCT((A2:A7 = E2) * (B2:B7 = F2)) > 0, "Valid Pair", "Mismatched Child Option")Try the example
Use the original values or change them to test your understanding.