Extracting and sorting data from source worksheet, Excel 2003

I have a problem which I am unsure Excel 2003 will be able to solve, but it would be great if it could. My workplace does not allow use of any unapproved programs and I am not going to go through a long laborious process to get a new software program approved. So suggestions of programs better suited to my needs won't help.

Basically I want a workbook which will have one input sheet where all the data is manually inputted. The other sheets then extract certain data and puts it all under relevant user defined headings. Please see the example below for a better explanation.

I have a workbook called ABC Company Employee List.xls. This contains all the employees within the company. I want there to be one worksheet which holds a universal list of all employees within the company, able to be changed at any time. I want other worksheets to hold certain lists based on the universal list, for example, I want a worksheet called 'Gender' which holds a list of all the employees divided up into male and female, with a heading row for male and a heading row for female. Another worksheet I might want to have is called 'Divisions' which splits the employees up based on which division they work for and puts a heading row with the division name at the top of each section. I also want a worksheet that is called 'Out of Country' which only has the employees who are working out of the country - I also want this worksheet to be split up by division with a header row for each division.

Any time I add an employee to the list, all I have to do is go to the universal list worksheet add another row and then all the other worksheets will amend themselves automatically. The same would happen if I took an employee off the list or changed some of the details of the employee.

So, my question is, does Excel have this functionality?

Absolutely. I'll post an example in 10-15 minutes.

Here are some samples I've created. The data is autopopulating to the other forms from the base set. It can easily pull across tabs as well.

top 5 autosort - revised.xlsx
index-match tutorial for tailz.xlsx

Thanks heaps for the answer! I'll work on getting my own spreadsheet to do this soon.