Data Validation

[Jumanarimawi]

Dears,

I'm new to this forum, and I have query

I have a request from client (long entery sheet) , one of the columns its the first one in the sheet a cliam number , the condition is to allow the user to duplicate the value only once , I tried to use this formula in the data validation custom bar
=COUNTIF($A$2:$A$2922,A2)=2 , but this doesnt allow to enter any value, im out of solutions , please help me guys

 

[Joe4]

Welcome to the Board!

Maybe this is what you need:
=COUNTIF($A$2:$A$2922,A2)<=2

 

[Jumanarimawi]

Welcome to the Board!

Maybe this is what you need:
=COUNTIF($A$2:$A$2922,A2)<=2

it works!! Mny thanks Joe4

 

[Joe4]

You are welcome.

I hope it makes sense why you want "<=2" instead of exactly equal to 2.

 

[Jumanarimawi]

You are welcome.

You are welcome.

I hope it makes sense why you want "<=2" instead of exactly equal to 2.

well not really, bjut I think not to specify it with 2 ?
I hope it makes sense why you want "<=2" instead of exactly equal to 2.

 

[Joe4]

By saying "=2", you are saying that it the count HAS to be EXACTLY 2. You cannot have 1. So if you tried to add a new one, it would fail, because 1 is not allowed (and you cannot jump from 0 to 2 in one step). So, you want to say it can be 1 or 2 (or "<=2").

Make sense now?

 

[Jumanarimawi]

By saying "=2", you are saying that it the count HAS to be EXACTLY 2. You cannot have 1. So if you tried to add a new one, it would fail, because 1 is not allowed (and you cannot jump from 0 to 2 in one step). So, you want to say it can be 1 or 2 (or "<=2").

Make sense now?

Crystal cleAR

 

[normpam]

it works!! Mny thanks Joe4

This CountIF with data validation only seems to work with absolute cell references; why does it not work with a named range?