Transfer data between sheets, validating 2 criteria.

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!

If you upload an example, it makes it easier to respond. I'm guessing a VLOOKUP (or HLOOKUP) would work but it's hard to tell without seeing the specifics.

I’m not sure if what you are trying to achieve can be done using formulas but I think this is what you were after. Just make sure that you enable content when you open the workbook. I did change a couple of formulas to make this work.

The program will fire off when you enter the hire date so make sure that you enter that in last.

Agentlist_example(1).xlsm

If you want it to update the list when you enter a termination date you will need to alter the code in Sheet 1. If you don't know how to do this just let me know.

After

Please Login or Register  to view this content.

Change

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.