VLOOKUP And Conditional Formatting

Hi, I have this formula in column F - =IF(ISNA(VLOOKUP(A4,'March 13 SOH'!A$1:D$178,3,FALSE)),"",(VLOOKUP(A4,'March 13 SOH'!A$1:D$178,3,FALSE)))

which returns the correct value according to the sheet it looks up.

In another cell I have conditional formatting for various colours depending on about three criteria, 2 of them relating to the F column.

The problem is that with some cells in column F, there is no match on the sheet that is looked up and this interferes with the conditional formatting.

When the above formula is taken out of these cells the conditional formatting works perfectly, however, I need the formula in the sheet for future use.

From playing around it looks lie this is the same if any formula is in the cells without a match, so maybe this isn't an issue with VLOOKUP?

Is there a way around this?

Thanks in advance

Thanks for the quick reply Dredwolf, but no joy.

Here is a link to a section of the sheet - http://wikisend.com/download/177288/...Book Short.xls

From row 5 to 15 are examples of the problem. The red cells in column N should be clear, no colour, the same as on row 16.
Rows 5 to 15 have no match in the "March13 SOH" sheet that is used to get the values into the F column.

Hopefully this makes sense?

Cheers

Here you go Dred.

Apologies, I should of explained this.

The cells in red are the ones that have been conditionally formatted.
If there is no value in the F column of that row, depending on if there are values under the months columns to the right will decide the colour.
As there is no value in the F column and no values in any of the month columns to the right, the red column should be empty, clear, like in row 16.
I think that maybe the columns that been CF'd need adjusting.

If you have a look under the Feb Wk 2 tab, that is what it should look like, but this is without the VLOOKUP formula.

Hope this explains a bit more.

You're correct, if no values in F or the months columns, both N & O should be blank.
Rows 5 - 15 are the problem ones.
Columns N & O have both been conditionally formatted. The idea is to put up different coloured flags if product is in stock but has/has not sold recently and we have stock.
If no stock (F) then N & O should not be coloured, even if there have been sales in the last 12 months, as in row 16.

Rows 5-15 have no match in the sheet March 13 SOH, so maybe this is the problem, because if a match is found, like row 16, it works correctly.
I have uploaded the workbook again and have put 1 into the March column in row 5, which has turned it green. It should only be green if we had stock.
For some reason it seems like the conditional formatted cells see a vlaue even though it is blank. I have also tried it with a 0 in F5, but no joy.

If the formula from F5 is deleted, Columns N & O work correctly.

Clear as mud? Hopefully not.

Cheers

Originally Posted by dredwolf

If Feb rules work, just apply the same to Mar, if Feb Rules do not work, then ... again confused

There are no formulas in the month columns. It is only some in column F (F5 - F15) in this example are being a pain.

Rows 16 and 17 work fine, but as mentioned above, that must be because they have a match for the Vlookup formula on sheet "March 13 Sales".

That's it Dred.

Trouble is, columns N & O don't see some F as blank, as mentioned above.

Superb!

Thanks for all of your help.