Forum Discussion
SergS
Jul 09, 2021Copper Contributor
UNIQUE Function in Data Validation
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?
- Jul 09, 2021
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#
SergeiBaklan
Jul 09, 2021MVP
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
Jul 12, 2021Copper Contributor
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.