Search/display a specific date from a seperate worksheet

I have the formula below working(Thanks to J. Topley for all his help),
=IF(COUNTIFS(ATMS!B:B,$G27,ATMS!H:H,"G557",ATMS!J:J,"C",ATMS!L:L,"="&"<60"),"<60",IF(COUNTIFS(ATMS!B:B,G27,ATMS!H:H,"G557",ATMS!J:J,"C"),"C",""))

What I would like to do, in a separate area of worksheet 1(Quals tab, highlighted in Yellow) is list the upcoming training, displayed on worksheet 2 for our employees. I would like to list the info contained in columns M(event start date/time) and N(bldg and classroom location). I have provided an uppdated sample.
Thanks.

In the example shown, You would show training due for two people because there are event start dates for them. Is this correct? Also how do you plan to clear the date when the course is completed?

I opted to go for a VBA solution for this since there is possibility that a person may be scheduled for more than one class which means looping through the data. The function is called GetTraining(Name). Where name is the cell that contains the person's name.

I suggest that you set up a list of employee names in a table and use this list for data validation both on the Quals Sheet and the ATMS sheet to make sure you spell the names the same way on both sheets.

I made use of an Excel table on the ATMS tab. Clear out that data and copy your real data in there. I do have a "standard" module to clear out a table and I can connect it to a button on the schedule sheet. Would you like this?

When I change it to IF(AND(, I get a #VALUE! result.....
=IF(AND(ATMS!B:B,$G70,ATMS!H:H,"G450RF",ATMS!M:M,"MM/DD/YYYY, HH:MM:SS"), IF(COUNTIFS(ATMS!B:B,$G70,ATMS!H:H,"G450RF",ATMS!M:M,"MM/DD/YYYY, HH:MM:SS"),"MM/DD/YYYY HH:MM:SS","NONE"))
I am trying to get Excel to return the Training #, Date, Time of the assigned training.

firstly remove merged columns from "O" to "U".
Add criteria in "O26" as Enroll
in O28 formula [Array formula hence : shift+ctrl+enter]

Formula:

Please Login or Register  to view this content.