Condition Formatting 1

[Sopman]

I have an SOP (Standard Operating System) that requires annual review. Each year I pass out the table of contents to each department manager and ask them to highlight the SOP’s they want reviewed, can’t do them all over 650 SOP’s. When I pass them out the managers want to see the previous years to see which ones they haven’t selected.

I made up a spreadsheet.
Column A – SOP Titles
Column B – SOP Number
Column C – 1999
Column D – 2000
Column E – 2001
Column F – 2002
etc…

I would like to put an “X” under the year in which that SOP was reviewed and have it change the text color for the SOP Title and Number.
For Conditional Formatting there are only three conditions that you can use. Is there a way to do multiple years (columns)???

 

[JVFriederick]

After you put an "X" under the year, do you want the SOP Title and Number to change if ANY X's are found?

 

[Sopman]

Yes, this would flag the managers that that SOP has been reviewed in the past.

 

[JVFriederick]

Well...... I thought that conditional formating could evaluate a formula, but it doesn't look that way.

Best I can suggest is to insert column C with a title of "Evaluated Previously". Then add the following formula :
=IF(COUNTA(D2:G2)>0,"Yes","&quot

Print this column along with the Titles and SOP Number.

 

[Sopman]

JVFriederick,

I was trying this formula:
=($C4+$D4+$E4+$F4=1)
It wasn’t proper programming, because the first review I would put a 1 in for that year and if it was getting review again, say, in two years I would have to put a 0, but it worked.
Looking at your formula it dawned on me to replace =1 with >0 and now it works perfect. I didn’t even think about that.

Thanks
Sopman

 

[JVFriederick]

No worries.