Tracking based on values in different columns

[bigbrain]

Good day,
I am trying to create a tracking system for tests. I have attached the file for easier explanation:

On Sheet 'Tracking', I would like to track those people (Staff no) who have completed all 3 tests (shown on sheet 'training').
For example: In cell B3, I would like to track staff no 2235 with an "x", if they have completed all trainings (sheet 'training', cells B8,C8 and D8).
The staff number is not fixed in a specific row on the training sheet, it can move up or down, so I can't use a formula that looks for that number in specific cell, it has to look in a range (A8:A14) to find it and then see if the cells B,C and D are all occupied. Only if all of them are (meaning tests 1, 2 & 3 have been completed, it needs to count them and give me an "x" on the Tracking sheet.

I hope this makes sense. I tried countif, but that only gives me a "1" and not an "x".

Thank you so much for your help!Tracktest.xlsx

[oeldere]

With an helpcolumn.

After that with VLookup (on the first sheet).

Or an pivot table.

See the attached file.

[xwarlock10x]

in cell B3 on the "tracking" sheet

=IFERROR(IF(VLOOKUP(A3,Training!A8:D14,AND(ISNUMBER(Training!B8),ISNUMBER(Training!C8),ISNUMBER(Training!D8))),"X",""),"")

then copy down

[bigbrain]

Oeldere, thanks so much, this works well, have to see if I can include this help column into the sheet. I think the pivot table won't work this time. But I really appreciate your help.

[bigbrain]

xwarlock10x, thanks so much for your help, I really like this formula, if I can't use a help column, this is the way to go. Really appreciate your help!

[xwarlock10x]

I'm glad it works for you..

[bigbrain]

xwarlock10x, sorry to bother you again, but I just realized, when I move the number on the training sheet to a different row, the formula doesn't recognize it anymore. I guess that's because you h ave "fixed" the row in the ISNUMBER part?!

[xwarlock10x]

can you upload your whole sheet.. and ill fix it for you?
i just done it to the sheet that you had here

[bigbrain]

I can't upload the actual sheet (confidential information), I had to recreate it. But I got oeldere's version working for me. Thanks so much, I really appreciate your time & assistance.