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 DataValidation
For Allow, choose Custom
In the formula box enter: =$A$3=6
Click OK

Danny wrote:
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?

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

On Aug 19, 9:33 am, Debra Dalgleish wrote:
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 DataValidation
For Allow, choose Custom
In the formula box enter: =$A$3=6
Click OK

Danny wrote:
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?

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

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

On Aug 19, 11:03 am, Danny wrote:
On Aug 19, 9:33 am, Debra Dalgleish wrote:





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 DataValidation
For Allow, choose Custom
In the formula box enter: =$A$3=6
Click OK

Danny wrote:
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?

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

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

- Show quoted text -

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.

Danny wrote:
On Aug 19, 9:33 am, Debra Dalgleish wrote:

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 DataValidation
For Allow, choose Custom
In the formula box enter: =$A$3=6
Click OK

Danny wrote:

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?

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



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

On Aug 19, 11:14 am, Debra Dalgleish
wrote:
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.





Danny wrote:
On Aug 19, 9:33 am, Debra Dalgleish wrote:

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 DataValidation
For Allow, choose Custom
In the formula box enter: =$A$3=6
Click OK

Danny wrote:

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?

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

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

- Show quoted text -

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


Danny wrote:
On Aug 19, 11:14 am, Debra Dalgleish
wrote:

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.





Danny wrote:

On Aug 19, 9:33 am, Debra Dalgleish wrote:

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 DataValidation
For Allow, choose Custom
In the formula box enter: =$A$3=6
Click OK

Danny wrote:

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?

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

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

- Show quoted text -


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?



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

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?

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.

On Aug 19, 12:28 pm, Debra Dalgleish
wrote:
Use the custom formula: =$A$4=0





Danny wrote:
On Aug 19, 11:14 am, Debra Dalgleish
wrote:

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.

Danny wrote:

On Aug 19, 9:33 am, Debra Dalgleish wrote:

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 DataValidation
For Allow, choose Custom
In the formula box enter: =$A$3=6
Click OK

Danny wrote:

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?

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

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

- Show quoted text -

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?

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

- Show quoted text -

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

On Aug 19, 1:57 pm, Danny wrote:
On Aug 19, 12:28 pm, Debra Dalgleish
wrote:





Use the custom formula: =$A$4=0

Danny wrote:
On Aug 19, 11:14 am, Debra Dalgleish
wrote:

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.

Danny wrote:

On Aug 19, 9:33 am, Debra Dalgleish wrote:

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 DataValidation
For Allow, choose Custom
In the formula box enter: =$A$3=6
Click OK

Danny wrote:

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?

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

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

- Show quoted text -

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?

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

- Show quoted text -

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 -

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