Custom Google Sheets Validation

I have a Google Sheets file that needs smarter data validation. The core task is to write custom formulas that power drop-down lists, so users only see context-appropriate choices as they enter data. Think multi-level or dependent lists that automatically expand when new items are added to the source tables, all handled through functions such as INDIRECT, ARRAYFORMULA, SORT, UNIQUE, and REGEX where useful.

Please work directly in my shared spreadsheet, comment each formula so future edits are clear, and include a brief note describing any named ranges you add. The job is complete when every designated column offers the correct dynamic drop-down options without manual maintenance.