Datavalidation problem

  • Thread starter Thread starter Jan Kronsell
  • Start date Start date
J

Jan Kronsell

I have a range, A2:A11. In cell I have this Datavalidation rule that
ensures, that the only tinh you can type is 1. (The figure 1). I A13 I have
SUM(A2:A11), and in A14 I have maybe 6. The value in A14 is the max value
that A13 must reach. My problem now is, how do I make a datavalidatuion
rule, that ensures, that only the allowed number of 1's i type within the
range.

I have tried things like
=A13<=A14
=SUM(A2:A11)<=A4
=A13<=A14=True

but nothing seems to prevent me form typing any number of 1's in the range.

Can anybody help?

Regards
Jan
 
You have to put the data validation in the cells where you're entering
the data, in this case cells A2:A11.

Select cells A2:A11 (with cell A2 as the active cell)
Choose Data>Validation
For Allow, select Custom
In the Formula box, enter: =AND(A2=1,$A$13<=$A$14)
Click OK
 
Hi,
Select cells A2:A11
Data validation:
=AND(COUNTBLANK($A$2:$A$11)>3,$A2=1)
This allows a maximum of 6 cells to have data, and restricts the data to the
number 1.
Regards - Dave.
 
Back
Top