unique() in data validation

[zoharb]

Respected,
I am referring to excel 2021
I can get the unique list elsewhere,
which i can later call as list in data validation
but direclty using unique() in CUSTOM of DATA is not working .
Can you please mention what is causing this error,
(my #SPILL question is also related to this )
Zohar Batterywala

 

[Eric W]

That's just the way it is. Some spill formulas can't be used by themselves within Data Validation. As you've found out, you can put the spill formula (UNIQUE in your case) in a cell elsewhere in your workbook, then use that reference, like =A1#, in your Data validation. If you have dependent Data Validation, or a column of Data Validation, that can get complicated and require a lot of cells, but that's the way it works.