Good day every one!

Been going over the forum and videos, but can't quite get it to work. I have uploaded an example file. >>CLICK<<

I have a table 'CREW' (sheet CREW), with all the staff. But the staff is divided in groups: Training, New, Eligible and Tech. Also, they have a status: Active or Terminated.

Now, what I need is a formula on the other sheets ((AGT New), (AGT Eligible), (AGT TECH)), that will validate two criteria: The group and the status.

What I am trying to achieve is basically the same as Advanced Filter does. BUT... I need it to be dynamic (therefor my choice to use tables). So each time the 'CREW' table changes (agents might be placed in another group), it will automatically update in the spreadsheets 'AGT New', 'AGT Eligible', 'AGT Tech'.

The formula I need would need to validate 2 criteria: Group and Active status. If one of them is not met, that employee will not be transferred to that groups sheet.

In the example file, this means that:
Employee's with Badge #'s 1001, 1002, 2003, 2004 are transferred to the sheet 'AGT New'.
Employee's with Badge #'s 1003, 1004, 1005, 2005 are transferred to the sheet 'AGT Eligible'.
Employee's with Badge #'s 3001, 3002, 3003, 3004, 3005 are transferred to the sheet 'AGT Tech'.
Employee's with Badge #'s 2001 and 2002 go no were. Their group is still 'Training'
Employee with Badge # 3005 goes no were either, his status is 'Terminated'.

So I would need a formula to achieve this, per sheet, that looks trough the CREW-table, finds the correct group listing AND checks if the employee is still active. I have looked on instruction videos and on the forum, but can't really find a workable solution.

Warm regards,
Chris!