Dynamic Column Range in a Sum Across Multiple Spreadsheet Formula

[ISTO]

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?

 

[ISTO]

Nobody has any potential solutions to this?

 

[jasonb75]

Are you trying to make the column ranges dynamic so that they only look at the used range rather than the entire column?

If that is what you are trying to do and you are doing it to make it more efficient then i suspect that your efforts will be counter productive.

If nobody else provides anything for you then I will have a look at it when I get chance although this may not be until early next week.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Dynamic Column Range in a Sum Across Multiple Spreadsheet Formula
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[ISTO]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Dynamic Column Range in a Sum Across Multiple Spreadsheet Formula
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Here as well:
Microsoft Tech Community

 

[ISTO]

Are you trying to make the column ranges dynamic so that they only look at the used range rather than the entire column?

If that is what you are trying to do and you are doing it to make it more efficient then i suspect that your efforts will be counter productive.

If nobody else provides anything for you then I will have a look at it when I get chance although this may not be until early next week.

Not sure I fully understand your question. I want the formula to look at the entire column (which is various weekly dates) in my Hidden Data spreadsheet and use the column to determine which cells to include in the parameters for summing.

 

[jasonb75]

If you mean that you want to use multiple criteria then you just need to use SUMIFS instead of SUMIF. From how you describe it in your reply it looks as if that is closer to what you need than a dynamic range, although whether it is actually what you need is not entirely clear.

A dynamic range (which you asked for) is one that resizes with the data by using an indexing function to find the last row (or column) with data in it so that the formulas are not processing the empty cells at the end of the range, not one that looks at the data based on specified criteria.