PDA

View Full Version : How to prevent Entry more than specified value.



frozenfire
12-25-2007, 09:20 PM
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
12-26-2007, 03:44 AM
Use Data Validatoion woth a formula of

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

Aussiebear
12-26-2007, 04:01 AM
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)