dynamically update several worksheets from a main worksheet based on criteria

Hello all,

Here is where I'm struggling. This has been beyond me, but I hope it isn't too large of a request for this forum.

I want to dynamically update several worksheets from a main worksheet based on criteria. I've attached a sample workbook.
I need to do this with formulas because this function will move to another person. No one here would be able to maintain the macros.

Here goes. I have a primary list of employees - thousands with some employee data. I want to create “team” tabs that pull specific employee information from the primary tab into their respective tabs based on the conditions in "team". Thus, employees who are in team red will have their information also on a team "red" tab, but I want to fill those tabs with only five fields of information not the complete list of variables.

Then, every time I update my primary list, I want formulas to automatically update the respective tabs with the pertinent information input into the primary tab based on the team criteria.
So, if I enter a new employee, John Smith on the team purple into the primary tab, his information is automatically entered into the purple tab.

I've been trying dynamic ranges, index and offset, but while I understand those concepts, I can't execute them in practice.

I would really appreciate help.

Thanks

Zeda