Formula???
 

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!

Formula???
 

Does this work for you:

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"John Sofillas" wrote in message
...
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!

Formula???
 

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


"John Sofillas" wrote:

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!

Formula???
 

On Aug 5, 3:32*pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
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



"John Sofillas" wrote:
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!- Hide quoted text -

- Show quoted text -

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