Make list and ignore empty values?

Hello,

is it possible to make a list (via data validation) and ignore all empty values / cells?
find attached an example what i mean)

When i open the drop-box in cell C1 i also get the empty cells in the list.

I found a solution with SORT / UNIQUE / FILTER - but this only works with the newer versions Excel 365 and Excel 2021.

Is there also a solution for that using Excel 2019 or Excel 2016?

I am assuming that this is just a piece of a bigger puzzle. In my example, I put the values in an Excel table and built a pivot table from them. The key point is the pivot table. You can build it from a hard-coded range if you prefer.

One of the things about pivot tables is that they can produce a sorted, unique list of values. I've shown the pivot table in column C. When the values are sorted in a pivot table, (blank) comes out on the bottom.

The next step is to overlay the results of the pivot table with a named, dynamic range. To do this click in cell C2 and select Formulas > Name Manager.

Select New and in the Name box type in a name such as MyList. Then in the refers to box type the following formula: =OFFSET(Tabelle1!$C$2,0,0,COUNTA(Tabelle1!$C:$C)-2,1)

Intellisense will fill in the "Tabelle1!$C$2" when you select that cell and the "Tabelle1!$C:$C" when you select that column. The rest you will have to type yourself.

Here is what the offset formula means =Offset(Start Here, Go down this many rows, Go right this many columns, Give me a range x rows deep and y columns wide).

So in our case, this means start in cell C2, go down zero rows and right 0 columns. So we are still in cell C2. then give me a range COUNTA(C:C)-2 rows deep and 1 column wide. I subtracted the 2 from the COUNTA because we do not want to "count" the header or the (blank).

With the values you have, this equates to Cells C2:C7. As values are added or deleted this range will grow and shrink accordingly.

All of this is wrapped up in a named range called MyList.

So then the validation (in Cell E1) becomes a list validation with the source being =MyList.

The pivot table can be anywhere in the workbook, even on a hidden sheet. I put it on the same page for convenience.

In D2

=IFERROR(INDEX($A$1:$A$10, MATCH(0, COUNTIF($D$1:D1, $A$1:$A$10&"") + IF($A$1:$A$10="",1,0), 0)), "")

enter with Ctrl+ShifT+Enter

D1 is blank or header ( i created named range LIST)

This is your DV list

List

=LIST