How to ensure that cell entry in a specific range is always unique?

[Mark McInerney]

Hi,

I have a range B1:B20. These cells are populated by the user and I want to ensure that no two entries are the same. Is there a way of presenting a dialog box that prompts the user to enter a different value if the value they are trying to enter is already entered somewhere in the range?

Any advice appreciated - Mark.

 

[njimack]

Use Data Validation. On the Settings tab, from the Allow Box, choose Custom. In the formula box, enter the following:
=COUNTIF($B$1:$B$20,B5)<2

 

[Mark McInerney]

Genius Neil - Many Thanks!

 

[Mark McInerney]

Hi Neil,

Slight problem - it works fine when checking the cell against the one above it.

It won't let me place "Joan" in B1 and B2, but it will allow me to place Joan in B5? Is there a way to avoid this?

Thanks - Mark.

 

[njimack]

My bad. Select the entire range, then enter the below as the Validation formula:
=COUNTIF($B$1:$B$20,B1)<2

 

[Mark McInerney]

Works a dream! Thanks Again!