A data validation dropdown isn't going to cooperate with you on this. It only wants to look at a static list, or at exactly the cells you tell it to, without thinking about what's in them.
Since we can't make it do what we want, let's compromise - we'll give it a continuous range that shows only the values we want to see (plus the blanks at the end, where people mostly won't look).
In my example, the cells that contain the values (and blanks) for my data validation list are A1:A15. I'm going to go to another column and type the following formula:
I need to enter this as an array formula, so I'll press CTRL + SHIFT + ENTER when I'm done typing the formula. Next, I'll copy/paste or fill down 15 rows. The result will be all of my non-blank values from A1:A15, then all my blanks at the end.
Now, I'll open the Data Validation dialog and point my list at the cells containing my formulas (not A1:A15). The blanks are still there, but at the end, so no one needs to see them unless they scroll down there to look at nothing.
It's not entirely clean, but it's dynamic and avoids macros.
So what did that formula do?
looks at the original range that has your values and blanks, and lets you tell it which cell you want to look at, by number.
looks at a set of numbers, counts up from the smallest, and gives one back,
checks whether each cell in your range matches anything but a blank string
returns the row number if it does,
and returns 999 if it doesn't. If you had more than 1000 rows, you'd need a bigger number here, but this is (hopefully) more than enough for your data validation choices.
The SMALL function now has a list of row numbers, with 999 in place of the actual row number for any blanks.
is a dynamic way to start counting from whatever cell you enter this formula in. As you copy the formula down, the number grows. This tells the small that you want the 1st smallest number in the 1st row containing the formula, the 2nd smallest in row 2, etc.
SMALL hands the row number back to
INDEX, which gives you the value for that row. Those 999s all turn into #REF errors at the end, but we quietly turn those back to blanks with the
That formula can be tricky to understand. If you're having trouble, try setting up sample data in A1:A15 on a new sheet, picking a cell in another column, and copying my formula exactly into the formula bar (don't forget CTRL+SHIFT+ENTER). Play with that a bit, and you should get the hang of it.