Solved

Conditional Format - Compare two cells for 3 results

Posted on 2014-04-15
3
318 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
What is format f12.8 for a CSV file 6 37
macro for closing opened workbook 6 18
Issues with DAX Calculated Columns 6 0
Dynamic Filter ? 3 0
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

914 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now