Datavalidation problem

[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

 

[Jan Kronsell]

I can add, that =COUNT(A2:A11)<=6 works, but =COUNT(A2:A11)<=A14 does not.

Jab

 

[Rick Rothstein \(MVP - VB\)]

Does this validation formula work?

=COUNT(A$2:A$11)<=A$14

Rick

 

[Debra Dalgleish]

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

 

[Jan Kronsell]

Thanks.

Jan

Does this validation formula work?

=COUNT(A$2:A$11)<=A$14

Rick

 

[Dave]

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.

 

[Jan Kronsell]

Hi Dave

Great.

Jan

 

[Dave]

You're welcome.
Regards - Dave