Using vlookup in a conditional format

[Ian_G]

Hi All,

Firstly, I apologise if this query has already been raised. I did look to see if there was a problem similar to the one I'm experiencing, but I wasn't able to find one.

Ok, here goes.

I am trying to highlight cell relating to quantity of available stock. The cells contents have nothing to do with the conditional format, they are just easiest place to put them to identify what I need.

In cell D2 I have the quantity of parts I need
In E2 I have the part number
In F2 I have the quantity that I can order. (This is the cell where I am applying my conditional format)
In Columns W and X I have a pivottable showing current stock held (Part number is in W, quantity of part is in X)

I want my conditional format to colour F2 red if the stock quantity in column X is lower than value in D2.

Please help, as my knowledge of combining IF and VLOOKUP to return a "greater than" or "lower than" value seems non-existent!

Thanks in advance.

 

[mzi]

Its very Simple
Select Cell D2
Then Select Home tab, then Conditional Formatting, then Highlight Cells Rules, then Less Than...
and finally type the formula
=$X$2<$D$2
and hit OK....

 

[Peter_SSs]

Ian

Try this formula in Conditional Formatting for F2:

=VLOOKUP(E2,$W$1:$X$1000,2,0)< D2

You may want to adjust the $1000 if you may have more or significantly less than 1000 rows in your pivot table. This number just needs to be bigger than the number of rows you will ever have.

 

[Ian_G]

Thank you peter! I knew I was being silly somewhere. In this case it was the (improper!) use of "<" and ">".

Mzi, I appreciate you input, but what I didn't tell you was that I also have more part numbers and quantities in the same row and each has to have the ability to be individually highlighted for issues.

 

[The_Barman]

Just a suggestion, but I would substitute VLOOKUP for SUMIF in this case.