Challenge 2: A Self-Maintaining Dependent Dropdown — Solution Walkthrough

Setup:
  1. Cell A2: Data Validation > List, source = Products[Category]
     (or =SORT(UNIQUE(Products[Category])) placed elsewhere and
     referenced with its # spill operator).
  2. Cell B2: Data Validation > List, source:
       =FILTER(Products[Subcategory], Products[Category]=A2)

Test: add a brand-new row to the Products table — an existing
Category (e.g. "Electronics") paired with a genuinely new
Subcategory (e.g. "Smart Watches") that didn't exist before.

Result: without touching either dropdown's Data Validation settings
at all, selecting "Electronics" in A2 and then opening B2's dropdown
shows "Smart Watches" alongside every subcategory that was already
there.

WHY THIS WORKS AS AN ANSWER
------------------------------
B2's dropdown source is a live FILTER formula, not a fixed, manually-
typed list of subcategories — it re-evaluates Products[Subcategory]
filtered to whatever Category A2 currently holds, EVERY time the
dropdown is opened, against whatever the Products table currently
contains at that moment. Since Products is an Excel Table (or backed
by one), adding a new row to it is automatically included in
Products[Subcategory] the instant it's typed in, and FILTER simply
picks it up the next time it runs.

Which chapter's own principle this demonstrates:

This is Excel Advanced Chapter 2's own tip box, in direct practice:
"A FILTER-sourced dropdown updates itself as the underlying data
changes" — the same "maintain the data, not the dropdown" principle
Chapter 1's UNIQUE and FILTER functions were built around in the
first place. No dropdown setting, no Data Validation dialog, ever
needs to be revisited again as Products grows.