How to prevent Entry more than specified value.

[frozenfire]

Hi, i want to know how to lock and unlock (or by validation) a specific cell with specific condition. I attached my file and description is given in that file only. Please do the needful as it is very imp and urgent for me.

[Bob Phillips]

Use Data Validatoion woth a formula of

=OR(AND(B1="Leave",COUNTIF($B$1:$B$10,"Leave")<=3),B1="Present")

[Aussiebear]

Use Data Validation with a formula of

=OR(AND(B1="Leave",COUNTIF($B$1:$B$10,"Leave")<=3),B1="Present")

Try the following
=OR(AND(B2="Leave",COUNTIF($B$2:$B$11,"Leave")<=$C$2),B2="Present")

I've changed the references from B1 to B2, the CountIf range to $B$2:$B$11, and changed the value 3 to the cell location, so that if the rule changed about how many could be away on leave at any one time, you just need to change the value in cell C2.

(Sorry Bob)