UNIQUE Function in Data Validation

[SergS]

I'm trying to use a Name, containing UNIQUE formula in Data Validation, but it throughs an error: The Source Currently Evaluates To an Error. 

Is it a bug?

[#2]

@SergS 

I'm not sure, but if UNIQUE doesn't recognize the numbers then there will be an error like this,

as far as I can dare remember.

It could be that there are numbers in one column

and numbers are entered as text in the other column.

Hope this tip can help you.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

[#3]

If I enter the same name just in the worksheets cell, than it spills the rows correctly. The problem appears only with Data Validation. Now I have found the same topic https://answers.microsoft.com/en-us/msoffice/forum/all/office-365-data-validation-error-when-using-u... with an explanation, that Data Validation does not work with Dynamic Arrays formulas. But I can't agree since OFFSET formula does work.

[#4 · Accepted solution]

@SergS 

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#

[#5]

@SergeiBaklan yes I did such a way with several helper sheets having TRANSPOSE(UNIQUE) and =$A$1#. It works, but bit slowly. Hoped there could be more elegant solution.

[#6]

You can solve this by using the offset formula in your data validation. =OFFSET(INDEX(INDIRECT(A1);1);;;COUNTIF(INDIRECT(A1);"?*");)
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];"?*");)

[#7]

@Jadore 

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. 

[#8]

@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.