First of all, the menu - formula, define the name, the name of a random, I take "color" as an example

References to the location of the input

=GET.CELL (63,Sheet2!$A1)+0/NOW ()

The above Sheet2! , depending on the sheet you are on, or you can delete it.

B1=Color

It's the name that was just defined

B25=LARGE(IF($B$1:$B$20>0,$A$1:$A$20,""),ROW(A1))

Then just pull down the dropdown and fill it.