Data Validation - two questions

[normpam]

I like to use named ranges a lot. I note that if I use a named range with Data Validation to NOT allow duplicate entries, such as:
=countif(Items,B2)=1, the Data Validation simply won't work. It seems to be forcing me to use:
=countif($B$2:$B$74,B2)=1

This is the first time I have found that a named range does not work exactly the same as using the actual cell addresses. Any thoughts? Is there a way around this?

Also, someone pointed out something to me a long time ago. The developers came up with a great feature with Data Validation, but it only works for actual data entry into a cell - if someone copies and pastes into the cell, Data Validation doesn't work at all. What would be a good way to keep someone from being able to copy a duplicate value in any given cell?
Thanks!

 

[maabadi]

I don't understand what you say Exactly.
1. Please upload your Example file & Desired Results With XL2BB ADDIN (Preferable) or free hosting site e.g. www.dropbox.com , GoogleDrive or OneDrive & insert Link here
2. If you want extract unique value, you can use different formula based Excel & OS Version. Please upload your account detail (click on your name , account detail and update and finally save it) to we can help to you based excel version.