Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|