Create Resourcing Financial Forecast Template for job leads to input into linking to master file

We are are a medium sized Architectural firm of 27 employees. Active projects vary from 20-40 at any one time 5 Project Leads are assigned multiple projects to manage the delivery of the project We operate a Forecasting Excel file that lists all active projects and includes the project job lead; project stage; the assigned monthly fee to one year ahead. This file is held under management control and totals up the forecasted monthly billing, critical to financial and HR projections This file is also used to assign resources to each project and can highlight resourcing pressure points (high monthly billing), or low monthly billing suggests staff are light on work. Management wish to allow project leads access to the listed projects assigned to them, to manage and adjust the forecast monthly billing and resourcing needed. We can see in Excel, how you can link individual cells from Sheets back to a main sheet - this is very time consuming and does not feel right to us. Is there a way of linking a block of cells from a Sheet back to a master sheet, which provides a global monthly total of all the projects. Also we'd like the resourcing element to be smart, in that we can allocate a staff member over multiple projects as a percentage of allocated time Alternative is a re-write? Attached is a snip of part of the current Master spread sheet (Master Forecast Snip), and what the Lead sheet could look like. Ideally the resourcing inputs would be weekly, or bi-weekly. and the % linked to an overall summary of individual team members list and % of work allocation. Many thanks and I look forward to your thoughts on this