Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
data validation
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
data validation
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
data validation
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
data validation
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
data validation
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
data validation
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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
data validation
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
data validation
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation Data using Validation Table cell range..... | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
Data validation with validation lists and combo boxs | Excel Discussion (Misc queries) | |||
Refresh existing data when changed in data validation list | New Users to Excel |