Combining data from multiple worksheets and inserting it in to a separate worksheet

Hello, first of all, this is a great forum, and while i was able to find many solutions to my problems while i was a guest here, i cannot find a relevant solution to my recent one...
I have a workbook, that will be used daily to enter people's presence or absence. The first 2 columns in a branch sheet have a name and last name value, the 3rd is the status (based on a list of allowed values), and the 4th is for the reason for absence, that the user must manualy enter. Now what i need to do, is to fill regions in the 3rd sheet, based on the value in 3rd column of the personnel lists (under the "status" entry), with name, last name, and the value in the remarks cell of that person. I hope that the example (using the fake names of course) i've added, will be enough to present a clear picture. Note that i should still be able to add or remove names and last names, to worksheets, and the function (or vba script) should still be working without modification for the new names.
Also note, that i'm using excel 2007, but have to save the workbook in 2003 format, to be used on older machines.

I thank you in advance for any help given

Thank you for the quick response. Sadly, the layout was predefined and non negotiable So I have to work with what i've got. I don't mind if i have to modify the formula for each worksheet, if i can only learn how to create one. The main problem i'm having is, that anything i come up with, seems to overwrite the first entry in my sheet3, without automaticaly moving to the next row. Well, to tell the truth, there was only one formula i've tried and it failed miserably. I'm actually a system admin, that also has to serve the whims of some people (that have offices many stories above mine, unfortunately) that do not know that there are many DIFFERENT branches of computer science. That's why i'm hoping you experts could help