Checking if employees have completed trainings? Vlookup, countifs, ... ?

Hello,

If someone can help me here, that would be really nice.
Background:
I am tracking people who have completed certain trainings, I get this data from database. Problem is that I need to manually check who has NOT done as data only contains people who have done training.

I have included an example document so you can get a better idea.

See below for comments per sheet:
Data Source: This data we get from our database. There is data only available for employee has tried to do test.
Staff: List of our employees
Courses: List of training courses

Since some courses are mandatory, I would need to know who hasn't done them yet.

Problem is some haven't even tried the test (missing from data) and some have tried, but have failed.

What would be the best solution to do this?

I would need to automate this to a list which I could pivot easily.

On tab "Manual" you see how it would be perfect but that is way too admin heavy as it is manual.

It would need to be dynamic and work in the future, when I add more courses to the list.

I've tried playing around with vlookup, countifs but I am currently stuck and can't think how could I achieve this.

Grateful for all the help!

Originally Posted by Miroslav R.

Hi there,
pls check the attachement:

Attachment 324852

Used dynamic named range for data input
Just add empl name and course a nd drag the formula.

Hope it helps

Wow! Thank you a lot! This example works very nicely, very nice work.

Using dynamic ranges (offset, counta) was totally new for me. I noticed using Name Manager all ended on $B, as an example:

Name: DataCourse
Refers to: =OFFSET('Data Source'!$C$1;0;0;COUNTA('Data Source'!$B:$B);1)

Does that need to be B:$B always? Please accept my apologies if question is dumb, first time I see dynamic ranges in use!

Another question, when I applied this technique to real data, I came across a problem:
Data from training system is chronological, this means that employee might have tried course, failed (Unsuccessful) and tried again and then passed it (Successful)
Currently data gets the first item (Unsuccessful) and stops after that. Is there an option to tell excel to look first if it can find "Successful"?