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?

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?

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?

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?

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?

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?

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?