Datavalidation problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jan Kronsell - 27 May 2008 17:52 GMT

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

Reply to this Message

Jan Kronsell - 27 May 2008 17:58 GMT

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

Jab

> 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
[quoted text clipped - 14 lines]
> Regards
> Jan

Reply to this Message

Rick Rothstein (MVP - VB) - 27 May 2008 18:41 GMT

Does this validation formula work?

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

Rick

>I can add, that =COUNT(A2:A11)<=6 works, but =COUNT(A2:A11)<=A14 does not.
>
[quoted text clipped - 18 lines]
>> Regards
>> Jan

Reply to this Message

Jan Kronsell - 27 May 2008 21:45 GMT

Thanks.

Jan

> Does this validation formula work?
>
[quoted text clipped - 23 lines]
>>> Regards
>>> Jan

Reply to this Message

Debra Dalgleish - 27 May 2008 19:08 GMT

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

> 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
[quoted text clipped - 14 lines]
> Regards
> Jan

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

Dave - 28 May 2008 02:20 GMT

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.

Reply to this Message

Jan Kronsell - 28 May 2008 13:28 GMT

Hi Dave

Great.

Jan

> Hi,
> Select cells A2:A11
[quoted text clipped - 3 lines]
> to the number 1.
> Regards - Dave.

Reply to this Message

Dave - 28 May 2008 13:42 GMT

You're welcome.
Regards - Dave

Reply to this Message