Dynamic Data Validation Lists from Changing Sources with INDIRECT & UNIQUE

This pattern creates dynamic, dependent dropdowns that update automatically as source lists change—ideal for data entry forms and CRMs. Maintain master tables for categories and subcategories; use Excel's UNIQUE (Office 365) to create spill ranges and name them dynamically via INDEX or LET. For legacy Excel, use OFFSET with COUNTA to define dynamic named ranges. Implement dependent validation using INDIRECT to reference the named sub-list based on the selected category. Add error trapping for blanks and invalid selections and a master validation sheet for governance. This reduces data-entry errors, enforces taxonomy, and feeds clean inputs into reports and pivots. Document the naming convention so future editors can extend lists without breaking validation.