Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting and validation
Hi all
Quick help: I have a worksheet were we enter a total number first , and then numbers for different arguments. For exmpl: A B C D E F Total Male Female Age40 Age41 Age42 12 7 5 6 4 2 etc. As they are all entered manually, I'd like to have some sort of conditional formatting in place if for A if A not=B+C or A not=D:F so the user can see if the entered figures don't add up. Any quick idea? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting and validation
Krissy
Highlight the rows that contain your data and try a conditional formatting with formula is: =NOT(AND($A2=$B2+$C2,$A2=$D2+$E2+$F2)) and set your formatting to highlight any discrepancies. If you are really interested in "quick" you may want to put in a formula to two and decrease your manual data input by 33%. Good luck. Ken Norfolk, Va On Apr 16, 10:03*am, Krissy wrote: Hi all Quick help: I have a worksheet were we enter a total number first , and *then numbers for different arguments. For exmpl: A * * *B * * * C * * * * *D * * * *E * * * *F Total Male Female *Age40 Age41 Age42 12 * * 7 * * *5 * * * * *6 * * * *4 * * * * 2 etc. As they are all entered manually, I'd like to have some sort of conditional formatting in place if for A if A not=B+C or A not=D:F so the user can see if the entered figures don't add up. Any quick idea? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting and validation
Thanks Kr
I tried, and it returned TRUE - but when I'm changing any number in the row it doesn't turn FALSE... Any more ideas? " wrote: Krissy Highlight the rows that contain your data and try a conditional formatting with formula is: =NOT(AND($A2=$B2+$C2,$A2=$D2+$E2+$F2)) and set your formatting to highlight any discrepancies. If you are really interested in "quick" you may want to put in a formula to two and decrease your manual data input by 33%. Good luck. Ken Norfolk, Va On Apr 16, 10:03 am, Krissy wrote: Hi all Quick help: I have a worksheet were we enter a total number first , and then numbers for different arguments. For exmpl: A B C D E F Total Male Female Age40 Age41 Age42 12 7 5 6 4 2 etc. As they are all entered manually, I'd like to have some sort of conditional formatting in place if for A if A not=B+C or A not=D:F so the user can see if the entered figures don't add up. Any quick idea? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting and validation
I forgot to write the formula comes up as FALSE when I enter it, but then as
soon as I enter any number in the row it turnes TRUE. "Krissy" wrote: Thanks Kr I tried, and it returned TRUE - but when I'm changing any number in the row it doesn't turn FALSE... Any more ideas? " wrote: Krissy Highlight the rows that contain your data and try a conditional formatting with formula is: =NOT(AND($A2=$B2+$C2,$A2=$D2+$E2+$F2)) and set your formatting to highlight any discrepancies. If you are really interested in "quick" you may want to put in a formula to two and decrease your manual data input by 33%. Good luck. Ken Norfolk, Va On Apr 16, 10:03 am, Krissy wrote: Hi all Quick help: I have a worksheet were we enter a total number first , and then numbers for different arguments. For exmpl: A B C D E F Total Male Female Age40 Age41 Age42 12 7 5 6 4 2 etc. As they are all entered manually, I'd like to have some sort of conditional formatting in place for A if A not=B+C or A not=D+E+F so the user can see if the entered figures don't add up. Any quick idea? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting and validation
Use a formula to set the condition:
=A2<(B2+C2) and set that color to red (or whatever) Set a second condition =A2<(D2+E2+F2) and set that color to yellow Format painter should work down the blank cells in column A because you entered the conditional format with relative references. If the first condition is met, then the second condition is not checked. If there are no problems, there should be no formats on the cell in column A. "Krissy" wrote: Hi all Quick help: I have a worksheet were we enter a total number first , and then numbers for different arguments. For exmpl: A B C D E F Total Male Female Age40 Age41 Age42 12 7 5 6 4 2 etc. As they are all entered manually, I'd like to have some sort of conditional formatting in place if for A if A not=B+C or A not=D:F so the user can see if the entered figures don't add up. Any quick idea? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting and validation
That works really well, thanks!
The only problem is I have 4 sets of figures which have to be validated this way and conditional formatting only goes up to three.. Any thoughts on that? "Brad Vogt" wrote: Use a formula to set the condition: =A2<(B2+C2) and set that color to red (or whatever) Set a second condition =A2<(D2+E2+F2) and set that color to yellow Format painter should work down the blank cells in column A because you entered the conditional format with relative references. If the first condition is met, then the second condition is not checked. If there are no problems, there should be no formats on the cell in column A. "Krissy" wrote: Hi all Quick help: I have a worksheet were we enter a total number first , and then numbers for different arguments. For exmpl: A B C D E F Total Male Female Age40 Age41 Age42 12 7 5 6 4 2 etc. As they are all entered manually, I'd like to have some sort of conditional formatting in place if for A if A not=B+C or A not=D:F so the user can see if the entered figures don't add up. Any quick idea? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting and validation
=OR(A2<(B2+C2),A2<(D2+E2+F2))
What this does is combines 2 of the checks into one. In this way, if either of those conditions is met, the conditional format will kick in. You will not be able to have 4 different colors to tell you what the problem is, but you will at least see that something is wrong. You could combine further if you wanted to like this: =OR(A2=(B2+C2),A2=(D2+E2+F2),A2=(G2-H2)) Just keep adding conditions separated by comma's. Many times it helps to practice the function using the function builder outside of the conditional format in a cell so that you have the explanations to work with. If you get a function working the way that you want it to, then copy and paste it into the conditional format. Another thing that you may want to do if you have 3 conditions is create a legend somewhere that identifies where the problem exists. If the color turns red vs. blue, for instance, color the headings blue that have the condition set to blue and the headings red for the red condition) "Krissy" wrote: That works really well, thanks! The only problem is I have 4 sets of figures which have to be validated this way and conditional formatting only goes up to three.. Any thoughts on that? "Brad Vogt" wrote: Use a formula to set the condition: =A2<(B2+C2) and set that color to red (or whatever) Set a second condition =A2<(D2+E2+F2) and set that color to yellow Format painter should work down the blank cells in column A because you entered the conditional format with relative references. If the first condition is met, then the second condition is not checked. If there are no problems, there should be no formats on the cell in column A. "Krissy" wrote: Hi all Quick help: I have a worksheet were we enter a total number first , and then numbers for different arguments. For exmpl: A B C D E F Total Male Female Age40 Age41 Age42 12 7 5 6 4 2 etc. As they are all entered manually, I'd like to have some sort of conditional formatting in place if for A if A not=B+C or A not=D:F so the user can see if the entered figures don't add up. Any quick idea? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation Conditional Formatting....or both? | Excel Worksheet Functions | |||
Stumped Conditional Formatting or Validation? | Excel Worksheet Functions | |||
Finding data validation and conditional formatting | Excel Discussion (Misc queries) | |||
Data Validation & Conditional Formatting | Excel Discussion (Misc queries) | |||
Effect of Conditional Formatting, Data Validation | Excel Discussion (Misc queries) |