Use LOOKUP to populate a resource allocation summary

[Jgoodsel]

I am trying to develop a resource allocation summary. I have a team of people working on different projects, each budgeted for different amounts over time. I have a workbook for each project that indicates, by person, allocation per month. In the attached (simplified) workbook, I have two projects: Project 1 and Project 2, with three resources each: Engineer, Director and Technician, each with work allocated across the six month period, Jan-20 through Jun-20. The Sheet Names are the Project Names.

What I need to do is develop a Summary sheet that compiles the project information by person, so I can see how each person is allocated across the months. I have populated the Summary sheet by "hard coding" the data from Project 1 and Project 2 sheets. However, because I actually have a team of over a dozen workers, and dozens of projects, I would like the summary sheet to automatically populate. In other words, I want to populate the data in C4:H5 based on the Position Name given in B3, the Project Name in B4:B5, and the Month Name in C3:H3.

I will then replicate to do the same thing to populate C9:H10, for "Director" in B8, and projects in B9:B10, across months C8:H8. And again, for "Technician."

From my searching, I think this can be done with the VLOOKUP (or maybe LOOKUP) function and the INDIRECT function (to call the proper Sheet Name based on the Project Name). However, I am not getting the syntax right, so am reaching out for help.

Thank you in advance.

[jeffreybrown]

Without using a lot of Indirect functions which could slow down your sheet, how about this