Challenge 3: A Dropdown Sourced from Only the First Spilled Cell — Solution Walkthrough

Setup: D2 holds =UNIQUE(A2:A20), currently spilling into D2:D6 (5
distinct values). A dropdown's Data Validation Source field is set
to just "D2".

What the dropdown actually shows:

Only ONE option — whatever the single value in D2 happens to be
(the first item of the unique list). The other 4 spilled values in
D3:D6 do not appear in the dropdown at all, even though they're
visibly sitting right there on the worksheet.

Why:

Data Validation's Source field, when given a plain cell reference
like "D2", only ever looks at that ONE cell's own value — it has no
special awareness that D2 happens to be the anchor of a larger
spilled array occupying D2:D6 as well. As far as the Source field is
concerned, D2 is just a single cell containing a single value, the
same as if you'd typed that one value in by hand.

The single change that fixes it:

Change the Source field from "D2" to "D2#" — adding the spill
reference operator from Chapter 1. This tells Data Validation to use
the ENTIRE current spill range as the list of choices, not just its
first cell, so all 5 unique values become selectable, and the
dropdown continues to grow or shrink automatically if the underlying
UNIQUE formula's result later does.

Notes:
- This is exactly the warning box scenario from this chapter: the
  dropdown doesn't throw any error and appears to work — it's just
  silently offering far fewer choices than intended, which can go
  unnoticed for a long time if nobody checks the dropdown against the
  full list it was meant to represent.