Master Spreadsheet Returning Values From other Spreadsheets

[mattfromcanada]

We complete a weekly shipping schedule, where Project Managers submit a hard copy of a shipping schedule to the Operations Manager. He then inputs that into a Master Shipping Schedule. The schedule is completed on Tuesdays for the following week (ie November 4th schedule released for week of November 10th). I've done this by having C1 on 'Master' as Today+6, which would be Tuesday-Monday. E1 is then returned as C1+4, which is a rough way of showing me the week of November 10-14. I've used the (today) function so it's live updated.

Is it possible to have individual PM's keep an up to date shipping schedules spreadsheet, and the Master Spreadsheet can draw from that and return the values if a specified date-range is included? I've attached four (4) spreadsheets - a master list and the three that it pulls from. The values are all returned, with the exception of one for PM3 as it does not fall into the date range.

My intention would be to have a system wherein the Project Managers keep their sheets up to date, and the Operations Manager would then open up his spreadsheet and it would pull the values from all the PM spreadsheets, generate his shipping schedule which he can then distribute.

Where I get mixed up is how/if Excel can 'force' install new rows. And I'd like to keep the sort function active so that they can view by PM, Project # etc.
But if there's nothing to show, ie. PM4 has nothing within the ship week, then nothing would return.

It's a challenge for me, but I hope someone can point me in the right direction....it's worked before!