Conditional Formatting question

[MikeyZ]

I have this fx in col "G".
=IFERROR(VLOOKUP(E9,'[QtyOnHand'!$A:$B,2,),"0")

It just pulls the on hand inv qty over from another sheet so I can compare it to yesterdays on hand qty I posted in F.

When qty differ between F and G I want to have either F or G change color. I picked red.
Problem is, if I have a 0 in F, the true value in G is #N/A and not 0. therefore I get a lot of "red" popping up. So I'm not getting a true picture.

Is there an easier way to achieve my goal?

 

[AliGW]

Can you give a link to the file via a file-sharing site? I'll have a look. The formula I gave you works perfectly here whatever the combination.

 

[AliGW]

As I said above, provide a link to the workbook.

 

[MikeyZ]

can't do that. company prohibits that from happening.
Maybe there's something else I can do !!

 

[AliGW]

The problem is that the issues you are having seem to be specific to the way your workbook is set up. Without seeing it, we're going to keep going round in circles, I fear.

 

[MikeyZ]

I don't agree 100% regarding how my wb is set up.
Both columns are set up in general format.

The sole problem is, in my opinion, is how the data in G is being compared to that in F.
I think it has something to do with the CF formula comparing F to G.
Everything is fine until G doesn't have any data in it and we change the #N/A to a 0 or a "".
As well as when F = 0 and G is a 0 or a "". the Formatting fails.

I had this problem once before and MR EXcel was able to solve the issue but that was a long time ago..

 

[AliGW]

Maybe we are just not quite understanding what outcomes you are expecting. How about providing a table of data showing some expected results? Even a screenshot of expected outcomes would help here.

 

[MikeyZ]

The data in G is pulled from a Pivot Table. If there is no P/N in E, then G is changed to 0.


E F G
674875 1000 596 In this line F changes color because G is either <> F
784118 500 0 In this case G is 0 because in the Vlookup fx, we used the IFERROR to change the #N/A to 0. F should turn color but it doesn't.!!
989115 0 0 F should not change color but currently it does for some reason.

So whatever is happening in G is preventing the CF to work properly.

I've tried altering the fx you suggested I use in CF but no matter what I try the end result is not right.
Here is I tried:
=IF(OR(F492="",G492=""),FALSE,F492<>G492)
I've tried using a 0 instead of "" in all different combinations.

There is nothing off in the cell formats, they're all equal.
I would think that CF should work but it's looking at the value in G in a strange way. Even though it should be cut and dry.

 

[AliGW]

You mention a pivot table: I wonder if that is the issue here.

 

[MikeyZ]

I know using pivot tables can be trying (I'm not an expert in this area, yet).
So I took the data from the PT and copied it into another sheet so I can use it.
Not crazy msg's to deal with.

 

[MikeyZ]

I GOT IT.

It was in the VLOOKUP fx.

=IFERROR(VLOOKUP(E9,'[QtyOnHand'!$A:$B,2,),"0")

It placed a "0" which is in TEXT format.
I changed it by removing the quotes, and it worked perfect.
=IFERROR(VLOOKUP(E9,'[QtyOnHand'!$A:$B,2,),0)

Sorry for the aggravation. My error, just never saw it.