Distribution of workload for multiple projects on a monthly basis

I am trying to create what our firm calls a resource tracker within one note. What this resource tracker does is allows the project manager to input the number of weeks a resource type is needed for each project for each month. The distribution of weeks needed for different resources varies from project to project, and not all resources are used on all projects. The goal is to take a long distance look at all projects in the pipeline versus current resources and confirm hiring needs and/or additional work needs. We have an excel spreadsheet that works right now, but it’s not connected to any of our other data, and it is also VERY static in terms of being able to expand resources and/or projects. I’ve started creating this in my current base, but I am concerned I’m heading down an unsustainable path. Any suggestion on how to make this a little more dynamic? Specifically, we want to have a 2-year look ahead. In order for that to happen, I need to add a new column and update my formulas each month. Guess this is a good time to point out this only updated monthly.

I realize I posted this on Good Friday in the US, and late in the afternoon … on a Friday … would appreciate anyone’s suggestion?

Thanks