Forum Discussion
UNIQUE Function in Data Validation
That's by design. UNIQUE() function returns an array, and data validation doesn't work wit arrays. It works with references on ranges. Thus you need to land returned by UNIQUE() array into the range and use reference on this range. For example, if in A1 is =UNIQUE(something) in data validation you could use the list with reference on spill =$A$1#
Where A1 is a text referring to a array defined in >Formulas>Name Manager. But you could use a column of a table instead. (=OFFSET(INDEX(tableName[columnName];1);;;COUNTIF(tableName[columnName];"?*");)
OFFSET/INDIRECT is even more slower. Didn't test the performance, but I guess significantly, depends on concrete data.
Fortunately in modern Excel we may not care about UNIQUE, it removes duplicates by default.
SergeiBaklan
This only appears to be correct when refencing the cells directly, when attempting to reference a table (thus requiring use of INDIRECT) it provides all values, to include duplicated content.
