Conditional Formatting help

[lynae42]

I'd like to highlight a row in conditional formatting -- only if the row contains the value of column D, and the Sum of Column L is equal to the value in column F.



I've tried a few formulas but to no avail, please let me know if clarification is needed as this may only be making sense in my head.

 

[Joe4]

Welcome aboard!

Yes, I think we need more clarification.

only if the row contains the value of column D

Do you mean if it contains a value in column D on that particular row (meaning, column D is not blank, but contains something)?

Sum of Column L is equal to the value in column F

Do you mean the sum of all rows in column L matches the amount in column F of that particular row?

When in doubt, you can always post a simple example for us to see what your data looks like (a picture often says a 1000 words!).

 

[lynae42]

No problem - So here's what I'd like to do, if it's even possible... -

The cells in Column D are titled Requisition Number, and there are several cells containing the same requisition number.

Cells in Column F are Quantity

Cells in Column L are the actual Quantity Received, but the numbers vary as materials are delivered at different time.

If it's even possible, I would like to highlight the row in CF if the sum of the Quantity Received does not equal the value of Quantity ... but only if they contain the same Requisition Number.

 

[lynae42]

In other words, I just want to highlight the row if we haven't received the total Quantity that we requested.

 

[Joe4]

Try this formula, set to work on rows 1:100 (so select rows 1:100 and enter this conditional formatting formula):

=AND($D1<>"",SUMIF($D$1:$D$100,$D1,$L$1:$L$100)<>$F1)

 

[roy00a4]

Hello lynae42,

We have all faced these queries with Conditional Formatting at point. I will try to help you with this.

Could you please clarify..

the row in CF

 

[lynae42]

You are an absolute life saver! Thank you!

Try this formula, set to work on rows 1:100 (so select rows 1:100 and enter this conditional formatting formula):

=AND($D1<>"",SUMIF($D$1:$D$100,$D1,$L$1:$L$100)<>$F1)

 

[Joe4]

You are welcome!