Need to validate contents of cells linked cells, highlighting those that require fixing

I am pulling data from a couple of sources into a single worksheet by operative to show activities that they have performed with regard to picking and or checking.

If they have performed a picking task there should be data in both Picking Labels (Column F) and Picking Hours (Column O) and conversely if they are performing a checking task there should be data in both Total items (Column N) and

However it is possible as the data is pulled from two sources that one entry doesn’t contain a value due to an error as shown on the attached worksheet. As this sheet will contain over 200 rows of data I need away to highlight the error to enable an operative to correct the mistake(s)
I thought this might be achievable through some sort of validation column(s) I attach a sample of what I am doing and highlight the discrepancies trying to highlight.

Once I have cracked this I would like to transfer the rows containing validated data to another worksheet as value only but also ensuring that there is only one entry per operative per day. hence why thought of validation column to drive criteria driven vba transfer, but need some help of the validation part first

Many thanks

Mark

I have come up with the following to check for value in both cells, if there is it provides OK or if there isn't ER

Formula:

Please Login or Register  to view this content.

As I have formula's in each cell using this doesn't work correctly, please see attached file: I have highlighted the rows where it is going wrong. Can Anyone help.

Cheers

Mark

Hi I have tweaked the formula and workbook to fix my own issue, however it doesn't appear to be fixing it. I am still getting incorrect values even though it all looks correct, I think it has to do with the referenced cells containing formula's as although the cells appear to return a blank or zero value they are being read as having a value.

e.g.
referenced cells F6 & O6 appear blank or hold a zero value; except for the formula's in both cells. This should mean that cell R6 is blank also however it shows "ValPH"

Referenced cells L7 & N7 again appear blank and therefore cell S7 should be blank also, however it shows "ValCH" The formula in S7 is as below:


[FORMULA]=IF(AND(LEN(L7)>1,(N7)>=0.25),"OK",IF(AND(LEN(L7)>1,(N7)<0.24),"ValTL",IF(AND(LEN(L7)<1,(N7)>=0.25),"ValCH","")))
[FORMULA]

If anyone could take a look and provide a suggestion that would be greatly appreciated

Cheers
Mark

Reviewed the formula's contained within the worksheet DataForm columns E through O and found that the Iferror element was causing issues with the formula's in cells within column P and Q. Once removed the [ ""] from formula

Formula:

Please Login or Register  to view this content.

then everything worked great.

so validation element is now fixed