Sorry I haven't replied sooner, I've been busy, and this particular problem takes a while to set up and test. The revised formula is:

Code:

=IF(AND(COUNTIF(INDEX('[Training tracker.xlsx]Training'!$E$5:$O$15,MATCH(D5,'[Training tracker.xlsx]Training'!$A$5:$A$15,0),0),"<"&TODAY())=0,INDEX('[Training tracker.xlsx]Training'!$P$5:$P$15,MATCH(D5,'[Training tracker.xlsx]Training'!$A$5:$A$15,0),0)>EOMONTH(TODAY(),-1),COUNT(INDEX('[Training tracker.xlsx]Training'!$Q$5:$S$15,MATCH(D5,'[Training tracker.xlsx]Training'!$A$5:$A$15,0),0))=3),"X","At least 1 out of date training")

This looks up the right row on the training spreadsheet, and requires that the values in E:O are all future dated, the value in P must be equal or after the first of the current month, and that the cells in Q:S are all dates. You'll have to enter this formula when the training spreadsheet is open, but you can close it after that, and the formula will read the closed workbook.

## Like this thread? Share it with others