Adding another formula

[Tazzy666uk]

So....... I very kind gentleman on here helped me with my spreadsheet. Essentially, I have a book with a sheet that has conditional formatting and formulas that then highlight on an 'alert' cell on another sheet.......

The formula (which works) that he created was:

[FONT=Calibri, sans-serif]=IF(OR(SUMPRODUCT(--(TRIM(INDIRECT("'CurrentStaff'!A4:AM"&COUNTA('CurrentStaff'!$A$4:$A$10000)+3))="")),COUNTIF(INDIRECT("'CurrentStaff'!AK4:AK"&COUNTA('Current Staff'!$A$4:$A$10000)+3),"<"&DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))),IF(SUMPRODUCT(--(TRIM(INDIRECT("'CurrentStaff'!A4:AM"&COUNTA('CurrentStaff'!$A$4:$A$10000)+3))="")),"Check","Check E"),"")[/FONT]

[FONT=Calibri, sans-serif]However, despite thinking it was finished and not wanting to bother the gentleman again, I have another bit that I missed, and these formulas are way over my head [/FONT]

[FONT=Calibri, sans-serif]I have a column (M) starting at M4 and going to M10000 that will have a date entered. If the date is more than 7 years old from the current date, it will fill yellow (done by conditional format, because I can manage this bit!!) It is also formatted that if blank=red, ok=white/clear.

I need my alert cell (E12) on the other sheet to change to amber & "Check E" as well, if over 7 years.

Alert cell (E12) on other sheet conditional formats are:

[/FONT]=LEN(E12)=7 (turns cell amber "Check E")

=AND(C12<>"",E12="") (turns cell green)

=AND(C12<>"",E12<>"") (turns cell red "Check")

Many Thanks in advance, there are some really kind & helpful people on here. I just wish I knew more about formulas



[FONT=Calibri, sans-serif]

<o></o>[/FONT]