?
Solved

Conditional Format - Compare two cells for 3 results

Posted on 2014-04-15
3
Medium Priority
?
327 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 26

Assisted Solution

by:Shaun Kline
Shaun Kline earned 600 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 1400 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

Independent Software Vendors: 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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

771 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