SOLVED

UNIQUE Function in Data Validation

%3CLINGO-SUB%20id%3D%22lingo-sub-2532454%22%20slang%3D%22en-US%22%3EUNIQUE%20Function%20in%20Data%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2532454%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20use%20a%20Name%2C%20containing%20UNIQUE%20formula%20in%20Data%20Validation%2C%20but%20it%20throughs%20an%20error%3A%26nbsp%3B%3CEM%3EThe%3C%2FEM%3E%20%3CEM%3ESource%20Currently%20Evaluates%20To%20an%20Error.%26nbsp%3B%3C%2FEM%3E%3C%2FP%3E%3CP%3EIs%20it%20a%20bug%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2532454%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2532646%22%20slang%3D%22en-US%22%3ERe%3A%20UNIQUE%20Function%20in%20Data%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2532646%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F159692%22%20target%3D%22_blank%22%3E%40SergS%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EI'm%20not%20sure%2C%20but%20if%20UNIQUE%20doesn't%20recognize%20the%20numbers%20then%20there%20will%20be%20an%20error%20like%20this%2C%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3Eas%20far%20as%20I%20can%20dare%20remember.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EIt%20could%20be%20that%20there%20are%20numbers%20in%20one%20column%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3Eand%20numbers%20are%20entered%20as%20text%20in%20the%20other%20column.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EHope%20this%20tip%20can%20help%20you.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20Community%20members%20reading%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2532813%22%20slang%3D%22en-US%22%3ERe%3A%20UNIQUE%20Function%20in%20Data%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2532813%22%20slang%3D%22en-US%22%3EIf%20I%20enter%20the%20same%20name%20just%20in%20the%20worksheets%20cell%2C%20than%20it%20spills%20the%20rows%20correctly.%20The%20problem%20appears%20only%20with%20Data%20Validation.%20Now%20I%20have%20found%20the%20same%20topic%20%3CA%20href%3D%22https%3A%2F%2Fanswers.microsoft.com%2Fen-us%2Fmsoffice%2Fforum%2Fall%2Foffice-365-data-validation-error-when-using-unique%2F916b59b5-9d32-4c64-bf22-c3973481f1e2%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fanswers.microsoft.com%2Fen-us%2Fmsoffice%2Fforum%2Fall%2Foffice-365-data-validation-error-when-using-unique%2F916b59b5-9d32-4c64-bf22-c3973481f1e2%3C%2FA%3E%20with%20an%20explanation%2C%20that%20Data%20Validation%20does%20not%20work%20with%20Dynamic%20Arrays%20formulas.%20But%20I%20can't%20agree%20since%20OFFSET%20formula%20does%20work.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2533293%22%20slang%3D%22en-US%22%3ERe%3A%20UNIQUE%20Function%20in%20Data%20Validation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2533293%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F159692%22%20target%3D%22_blank%22%3E%40SergS%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20by%20design.%20UNIQUE()%20function%20returns%20an%20array%2C%20and%20data%20validation%20doesn't%20work%20wit%20arrays.%20It%20works%20with%20references%20on%20ranges.%20Thus%20you%20need%20to%20land%20returned%20by%20UNIQUE()%20array%20into%20the%20range%20and%20use%20reference%20on%20this%20range.%20For%20example%2C%20if%20in%20A1%20is%20%3DUNIQUE(something)%20in%20data%20validation%20you%20could%20use%20the%20list%20with%20reference%20on%20spill%20%3D%24A%241%23%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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?

 

 

4 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 (New 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.