Dynamic Column Range in a Sum Across Multiple Spreadsheet Formula

[istoadvisors]

I am using weekly timesheet data that is copied into separate spreadsheets tabs. I am using a report spreadsheet to aggregate the data for weekly numbers and cumulative to-date figures based on project. I figured out how to add values across multiple spreadsheets using SUMPRODUCT/SUMIF/INDIRECT. The problem I am running into is how to create a dynamic range column in the SUMPRODUCT/SUMIF/INDIRECT formula based on the report date to sum my cumulative hours to-date on the project. The report date is set at the top and most of the other formulas on the spreadsheet are based on this value. I attached a photo of my current formula. Can anyone provide me a possible solution to my problem or let me know what additional data they need to figure out what I'm trying to accomplish?

[#2 · Accepted solution]

The best solution may not be the one you're looking for in this case. Take a step back and consider the future of the workbook and what will happen with more data being added as time goes on. Your formula works for the present state of the workbook, but will it hold up with updates and be easy to manage?

You might consider consolidating similar data in 1 sheet. It may take some re-structuring, but it would be much easier to analyze and maintain.  This might be the best option without access to VSTACK.