Main sheet populated by multiple sheets, dynamic ranges

Hi all,

I've seen a few solutions that seem to *almost* address the problem I'm trying to solve... This is the most ambitious thing I've attempted in Excel. I'm not new to visual basic, but it's been a while. Certainly haven't done any coding in Excel.

Here's the skinny (file attached):

Each PO (Project Officer) has his or her own worksheet (labelled “Sleepy”, “Doc”, “Bashful”, and “Not Assigned”). These are the worksheets into which changes will be entered by the POs. The worksheet called "MAIN" needs to populate automatically with data from the PO worksheets as the POs make changes/deletions/etc to their entries. All worksheets have the same column headings. The number of rows in each PO worksheet (Sleepy, Doc, Bashful) will change as POs are assigned projects/complete projects.

1. You will notice that “MAIN” is blank right now. “MAIN” needs to be populated by data from the PO's individual worksheets, and be updated automatically as the POs make changes. “MAIN” needs to show all projects by all POs. Not necessarily in any particular order, they just need to be there.

2. “Not Assigned” worksheet also has a column labelled “PO”. Each cell in that column has a drop-down menu from which a PO can be chosen and thus assigned to a project. Once a project has been assigned to a PO, the whole row needs to disappear from the “Not Assigned” worksheet, and appear in the worksheet belonging to that PO. That drop-down menu shouldn’t be available anywhere other than in the “Not Assigned” worksheet.

Any thoughts/suggestions would be appreciated!

Kindest thanks,
cmack