Consolidating dynamic lists from multiple worksheets to new workbook

Greetings. I'm an Excel novice with minimal VBA programming experience. This is also my first post, so my apologies if it is difficult to grasp my situation. What I am trying to accomplish is to create a consolidated "master list" in a separate workbook by extracting data lists (and based on specific criteria) from multiple worksheets.

Scenario:
- Multiple individual users will fill out a weekly worksheet entitled "Pipeline" that indicates the status of their assignments (i.e. "1-Open", "1-Retained", "2-Closed", etc.) plus other related information.
- Worksheets all have the same format. For example, column heading in B7 is "Status" and assignment entries will start from B8, B9, etc. where they indicate the status.
- Assignment status will change weekly on each worksheet, as well as the number of assignments. Therefore the number of (row) entries will progressively increase as new assignments are received.
- Each worksheet has cells where they enter their "name" and "team name"

Objectives:
1. Would like to create a macro that extracts and lists only the rows that have the "1-Open" and "1-Retained" status from these individual worksheets into master list in a new workbook.
2. Would like this master list to update dynamically as the individual worksheet data changes
3. Finally, it would it would be perfect if on the new master list it could also include the person's "name" and "team name" next to the respective assignments.

I have attached a sample of the worksheets, and master list that I am trying to compose. I am using Excel 2003.

Again, I am a complete rookie to VBA and any assistance would be most appreciated. I have been searching the forums for similar situations, and although there were some similar cases, I just wasn't able to comprehend how to put this all together.

Cheers! -kmsosaka in Japan