data validation

  • Thread starter Thread starter Danny
  • Start date Start date
D

Danny

I would like to know if it is possible to use data validation on a
field that is summing/totalling data. IE total 1+2+3=6. is it
possible in the total field to use data validation so if the total
is
not equal to 6 then error?

I have tried this by establishing sum field totalling a1...a5 in A6
field, but when I go into input data into a1..a5 and it totals the
data validation on A6 doesn't provide an error message?


Any help is greatly appreciated



By the way I am using Excel 2003 as the application
 
Put the data validation on the cells where the values are entered. For
example, if the 1,2,3 are in cell A1:A3, and the total is in A4:

Select A1:A3
Choose Data>Validation
For Allow, choose Custom
In the formula box enter: =$A$3=6
Click OK
 
Put the data validation on the cells where the values are entered. For
example, if the 1,2,3 are in cell A1:A3, and the total is in A4:

Select A1:A3
Choose Data>Validation
For Allow, choose Custom
In the formula box enter: =$A$3=6
Click OK
Click to expand...

Thanks for the help. Custom allows me to do the following =A1:A3>0 so
if the total in A4 is less than 0 then it works. Thanks for showing
me custom
 
Thanks for the help. Custom allows me to do the following =A1:A3>0 so
if the total in A4 is less than 0 then it works. Thanks for showing
me custom- Hide quoted text -

- Show quoted text -
Click to expand...

Sorry I mispoke. So if I do custom and select A1:A3 when I place a
negative number into the field I get the validation error. What I
would like is if I put any values in A1:A3 it accepts those values.
What I want is the validation rule to come up if the total of A1:A3 is
less than 0

Is that possible?
 
You're welcome, and thanks for describing how you got it to work.
In my instructions, there's an error in the formula, which should be:
=$A$4=6
so it refers to the cell with the total.
 
You're welcome, and thanks for describing how you got it to work.
In my instructions, there's an error in the formula, which should be:
=$A$4=6
so it refers to the cell with the total.







--
Debra Dalgleish
Contextureshttp://www.contextures.com/tiptech.html- Hide quoted text -

- Show quoted text -
Click to expand...

Well I was wrong because if the person enters a negative amount in say
a1 the total is negative and it won't allow me to continue entering
because the validation rule kicks in due to the total being less than
zero. Any Ideas?
 
Use the custom formula: =$A$4>=0

Well I was wrong because if the person enters a negative amount in say
a1 the total is negative and it won't allow me to continue entering
because the validation rule kicks in due to the total being less than
zero. Any Ideas?
Click to expand...
 
Well I was wrong because if the person enters a negative amount in say
a1 the total is negative and it won't allow me to continue entering
because the validation rule kicks in due to the total being less than
zero. Any Ideas?
Click to expand...

Maybe a custom formula like this would help:
=OR($A$1="",$A$2="",$A$3="",$A$4=6)
It assumes that the three cells start all empty, they get entered in any
order, and the check gets activated when the last one is entered.
 
Use the custom formula: =$A$4>=0







--
Debra Dalgleish
Contextureshttp://www.contextures.com/tiptech.html- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks for your response. Here is the steps I am using. I hit
Autosum icon on tool bar. I select a range of A1:A3. I then select
A1:A3 and go to Data > Validate. I plug in =$A$4>=0 under the
Custom. When I enter the data into A1 as a positive it is fine. When
I enter number as a negative I get the validation error. It appears
that if the sum field in A4 is a negative number than it will activate
validation rules. I am not sure if this is possible. To allow all
entries into A1:A3 to be input before the validation error, in other
words if the total of those 3 are negative, then the validation error
activates??? is this possible?

Again I appreciate your help. Let me know if I am explaining this
wrong or if I am doing the wrong steps.

Thanks
 
Thanks for your response. Here is the steps I am using. I hit
Autosum icon on tool bar. I select a range of A1:A3. I then select
A1:A3 and go to Data > Validate. I plug in =$A$4>=0 under the
Custom. When I enter the data into A1 as a positive it is fine. When
I enter number as a negative I get the validation error. It appears
that if the sum field in A4 is a negative number than it will activate
validation rules. I am not sure if this is possible. To allow all
entries into A1:A3 to be input before the validation error, in other
words if the total of those 3 are negative, then the validation error
activates??? is this possible?

Again I appreciate your help. Let me know if I am explaining this
wrong or if I am doing the wrong steps.

Thanks- Hide quoted text -

- Show quoted text -
Click to expand...

perfect the =OR($A$1="",$A$2="",$A$3="",$A$4>0) worked Thanks to
everyone that helped on this I really appreciate it.
 
Back
Top