Excel restrict duplicate entry from two lists

[Charles Bushby]

I wish to use data validation to restrict duplicate entry into a cell from two lists.

I can set it up for example using =COUNTIF($B$2:$B$80,B39)=1 in data validation but I need to also check against entries in cells F2 to F80 to ensure a duplicate is not entered in B39.

Any ideas how this might be achieved?

 

[meldoc]

Hello

I think this should do it, you will have to adjust the ranges to suit.

=SUM(COUNTIF(B2:B6,G2)=1,COUNTIF(F2:F6,G2)=1)=1

 

[Charles Bushby]

Brian,

That works perfectly. Many thanks

Charles