Formula???

[John Sofillas]

Hi,
I am looking for a formula that does the following.....

C1 through C16 values are set to 0
B1 has a value of 100
Data entered into C1 through C16 has to be greater than or equal to
B1, but can be 0 (it just can't be 1 - 99)
In cell D1 I need a formula that results with any type of notification
that a number has been entered into C1 through C16 1 - 99

Any assistance on this would be greatly appreciated. Thanks!

 

[RagDyer]

Does this work for you:

=IF(SUMPRODUCT((C1:C16>0)*(C1:C16<B1))>0,"BAD DATA","")

 

[Jim Thomlinson]

If I were doing that I would use a combination of Validation and Conditional
Formatting...

Select the range C1:C16
Select Data -> Validation | Custom
Add this formula
=OR(C1 = 0, C1 >= $B$1)
Select the Error Alert tab and Change from Error to Warning
Select Ok

Select Format -> Conditonal Formatting
Change from Cell Value Is to Formula Is
Add this formula
=AND(C1 > 0, C1 < $B$1)
Click the format button and Select a Pattern or such as your alert.
Select Ok.

 

[John Sofillas]

If I were doing that I would use a combination of Validation and Conditional
Formatting...

Select the range C1:C16
Select Data -> Validation | Custom
Add this formula
=OR(C1 = 0, C1 >= $B$1)
Select the Error Alert tab and Change from Error to Warning
Select Ok

Select Format -> Conditonal Formatting
Change from Cell Value Is to Formula Is
Add this formula
=AND(C1 > 0, C1 < $B$1)
Click the format button and Select a Pattern or such as your alert.
Select Ok.
--
HTH...

Jim Thomlinson







- Show quoted text -

This is EXACTLY what I was looking for. Thanks RagDyer.