Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
LIVE
SOLVED

UNIQUE Function in Data Validation

Copper Contributor

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?

 

 

7 Replies

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

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.
best response confirmed by SergS (Copper Contributor)
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#

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

 

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];"?*");)

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

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

1 best response

Accepted Solutions
best response confirmed by SergS (Copper Contributor)
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#

View solution in original post