"Biff" wrote...
That countif formula would require that it be copied the
length of entire list of values, ~30K.

This formula is much better and eliminates blanks. If the
list to extract values is in A1:A30000, leave cell B1
empty and enter this ARRAY formula in B2:

=INDEX($A$1:$A$30000,MATCH(0,COUNTIF
($B$1:B1,$A$1:$A$3000),0))

Copy down until you get #N/A errors.
....

Since it appears the data changes every month and presumably remains fixed
during the month, better to use an advanced filter and just make that part
of the monthly data revision process.

If you must use formulas, and the dynamic data range were named BigRange and
the defined name RowsInBigRange were defined as =ROWS(BigRange), and if
there would never be more than, say, 100 distinct items, select a 100 row by
1 column range and enter the array formula

=T(OFFSET(BigRange,
SMALL(IF(MATCH(BigRange,BigRange,0)=ROW(INDIRECT(" 1:"&RowsInBigRange)),
MATCH(BigRange,BigRange,0),""),ROW(INDIRECT("1:"&R owsInBigRange)))-1,0))

If this were entered in X1:X100, then define DistinctItems referring to

=OFFSET($X$1:$X$100,0,0,COUNTIF($X$1:$X$100,"<#NU M!"),1)

and use DistinctItems as the source for the validation drop-down list.