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?

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#

UNIQUE Function in Data Validation | Microsoft Community Hub

SergeiBaklan
MVP
Jul 09, 2021
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#
- SergS
  Copper Contributor
  Jul 12, 2021
  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.
NikolinoDE
Gold Contributor
Jul 09, 2021
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.
- SergS
  Copper Contributor
  Jul 09, 2021
  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-unique/916b59b5-9d32-4c64-bf22-c3973481f1e2 with an explanation, that Data Validation does not work with Dynamic Arrays formulas. But I can't agree since OFFSET formula does work.
Jadore
Copper Contributor
Oct 03, 2022
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];"?*");)
- SergeiBaklan
  MVP
  Sep 06, 2023
  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.
  - Tim__Bailey
    Copper Contributor
    Dec 07, 2023
    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.

Forum Discussion

UNIQUE Function in Data Validation

Share

Resources