Solved

Conditional Format - Compare two cells for 3 results

Posted on 2014-04-15
3
325 Views
Last Modified: 2014-04-15
I have two columns in an excel sheet that show printer counts as recorded by our staff (count) and the second shows the count(s) from the invoice (invcount).

printer serial   |       count |   invcount

I only log something in the invcount if the invoice does not match our count from the panel.
I would like a formula that compares count to invcount and colors the cells depending on the comparison  (keeping the number in tact)
orange = over (greater than than the count column)
blue = under (less than the count column)
no fill = if empty (or zero difference - not logged)

I have headings at the top of the cells. F1-F3,G1-G3
I have tried using conditional formatting with icons but have an issue with no less than selection.  I am also finding it difficult to compare two entire rows without the SUM of each row deciding the value.
It would be nice to compare left to right down the entire columns and not add formatting to each cell that I logged.

Thanks in advance
0
Comment
Question by:PostQ
3 Comments
 
LVL 26

Assisted Solution

by:Shaun Kline
Shaun Kline earned 150 total points
ID: 40002543
Assuming your Count column is B, your Invoice column is C and your data starts at row 2, you can use these two conditional formulas:
For Blue Fill
=AND($B2<>"",$C2<>"", $B2>$C2)

For Orange Fill
=AND($B2<>"",$C2<>"", $B2<$C2)

Set the Applies to for both formulas to include columns A to C: $A:$C
0
 
LVL 14

Accepted Solution

by:
sentner earned 350 total points
ID: 40002551
You just need to use conditional formatting with formulas, with 2 rules.  See attachment.
condformat.xlsx
0
 
LVL 2

Author Closing Comment

by:PostQ
ID: 40002600
I like the simplicity of the conditional formatting.  I will be working with excluding the printer name from being colored.  Just limiting it to the invcount cell.
invcount cell will take the color if above or below the value in the count cell.
Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will show you how to use shortcut menus in the Access run-time environment.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question