How can I use conditional formatting to mark up this set of error conditions?

Hi all.

I have a lot of data (regarding graffiti) in a table and all my formulas work well. But, because I sometimes make an error in two columns of data I sometimes get results that do not balance. I want to put in a conditional format (?Is there a better way?) that will highlight the error as I enter the data. Can you help please?

Using Excel 2003 (sp3) and no add ons. There are lots of identical rows, 5:400, making up a table. I will use row 10 as an example.

In column M there is a formula which may or may not give a visible result. (It's a measure of the number of days taken to clean the graffiti and will only hold a result if the job has been finished.) The result for "M10" will be a number between 0 and 5 90% of the time, but may creep up to 30. It may also leave the cell looking blank although the formula itself is still lurking out of sight.

A matching row of cells, O10:T10, have no formulas in them. I must manually enter a "w" or an "a" into ONE of these columns, depending on the graffiti location (Worthing or Adur) and property ownership (Columns for residential, commercial, council etc.)

So now the errors I need to flag up as I enter data are to ensure that M and O:T balance. There should always be a result in M if there is an entry somewhere in O:T and if there is an entry in M there must be an entry somewhere in O:T.

Sometimes I mistakenly put an entry in O:T even if there was no result in M. Sometimes there is a result in M and I fail to put an entry in O:T. I need to be alerted as this happens, if possible. It will save hours of trying to track down my data entry errors.

BUT, there is already one conditional format already in M. It highlights the cell if it goes over 5 days for cleaning. =ISNUMBER(M10)*(M10>5).

If you can help, thank you very much. I thank you, my wife thanks you for me not doing overtime trying to track down my errors, my boss's thank you for making me more efficient and other forum readers may thank you for helping them to improve their sheets.

One way,

Use a helper column, I used U.
This will display 1 or 2 if the data is incomplete.

=IF(M10<>"",IF(COUNTA(O10:T10)>0,"",2),IF(COUNTA(O10:T10)>0,1,""))

Then use conditional formatting in M to flag missing data
and in O:T

See attached.

Originally Posted by Andy Pope

One way,

Use a helper column, I used U.

Andy, you are a lifesaver; that is perfect. Thanks very much. Your demo explains it better than the words. Have a (virtual) drink on me.

Originally Posted by clownfish

you could also try this:

Hi Clownfish.

Thanks for a second idea on how to do this. It's similar, using an extra column with formulas to highlight the errors, just different. I shall trial both ways and see what works for me. Have a virtual drink too!

Andy! A simple question from a retard like me... =IF(M10<>"",... how to modify this bit to say if column M10 OR column N10 <>""? I tried (M10:N10) but got a VALUE error. As you can tell I use Excel because I have to not because I'm qualified!

Thanks.

You can use the OR function,

=IF(OR(M10<>"",N10<>""),IF(COUNTA(O10:T10)>0,"",2),IF(COUNTA(O10:T10)>0,1,""))

Originally Posted by Andy Pope

You can use the OR function,

=IF(OR(M10<>"",N10<>""),IF(COUNTA(O10:T10)>0,"",2),IF(COUNTA(O10:T10)>0,1,""))

Andy, you are a lifesaver. Thanks and my apologies again for PMing you.

FYI the error catching has worked well in it's M only form and once I pasted it into my sheets the errors just jumped up at me. I'll modify it to the M&N later today but for now my books balance.