Excel formula need help

[Abhijeet]

Hi

I have excel data in Column I Names and i need When in Column Z try to input name when same row match same name then not allow to insert

I tried with If Formula but not know how to do in Data Validation please tell me

=IF(I2=Z2,"Not allow","allow")

 

[shrivallabha]

In cell Z2 apply below Data Validation by selecting Data Validation >> Custom
=I2<>Z2
Then copy this cell and do Paste Special >> Validation.

 

[Abhijeet]

This is work but if any one copy paste on that cell then DV rule break how to protect DV rule or any other way to do this

 

[shrivallabha]

There's no straightforward method to provide protection against this. See if you can use these references:
http://superuser.com/questions/870926/restrict-paste-into-dropdown-cells-in-excel

http://forum.chandoo.org/threads/data-validation-list-override-when-copy-pasted.11933/