Is there a way to apply a data validation to a cell from a list that is in an excel table?

Question

I have a source list in a dynamic excel table. The column of the source list that I want to reference for data validation has 3 cells populated but there are a few extra cells below the last populated cell that are blank. I want to set a dynamic data validation list that references the column of the source table so that it selects only the cells that have data and doesn't show the blanks as part of the drop down list.

I have used variations and combinations of "FILTER" and "INDIRECT" formulas but excel either returns that the data validation has an error or the cell drop down still has blanks as part of it.

The table is called "LOGIC". the column is called "Data". A few examples of what I have tried are below: I have tried:

1. =SORT(UNIQUE(FILTER(LOGIC[Data],LOGIC[Data]<>"")))
2. =SORT(UNIQUE(FILTER(INDIRECT("LOGIC[Data]"),INDIRECT("LOGIC[Data]")<>"")))
3. =LOGIC!$A$2:$A$10 - works but return still includes blanks
4. =INDIRECT("LOGIC!$A$2:$A$10") - works but return still includes blanks
5. =LOGIC!$A$2:$A$5 - works but is not dynamic
6. =INDIRECT("LOGIC!$A$2:$A$5") - works but is not dynamic

Answer 1

I believe the problem is because formulas created with functions that return dynamic arrays are not accepted by Data Validation.

So, using SORT, UNIQUE or FILTER presents a problem.

If you can create a formula that returns a reference, and not an array, you can use it.

So, this will work:

=INDEX(LOGIC[Data],1,1):INDEX(LOGIC[Data],COUNTA(LOGIC[Data]),1)

The issue is that this will not sort the items in the way you wanted. A workaround for that might be to sort the table in advance.

If you can't sort the table, and you must have the items sorted, then you will need to store the unique sorted items in the spreadsheet somewhere, and point the data validation to that dynamic array reference.

For that you can use the formula above, or your original formula:

=SORT(UNIQUE(FILTER(LOGIC[Data],LOGIC[Data]<>"")))