Dynamically Sum Cell Across Multiple Worksheets

[Mplz]

Hello all, happy holidays!

I am trying to write a formula or VBA script that will allow me to sum data in a cell across multiple worksheets but be able to set the range of the worksheets. We have a daily summary with metrics broken out on separate sheets for each day. I am trying to automate pulling the weekly data into another file to give as a recap. The sheets are saved as the date (12.11.2024 etc). I am summing data from the same cell for the entire week. '

Doing this manually the formula would be =SUM('[DailyTracker.xlsx]12.8.2024:12.14.2024'!$B$11). What I am trying to do is have two cells where I can define that date range, so I don't have to update the formula each week. I'm going to be doing this with multiple cells so having a single reference point to change that replicates out to the 10 or so metrics I'm trying to pull would save so much time. I've tried doing a combination of SUM and INDIRECT while concatenating the dates, but I can't figure out how to get it to reference another file with those ranges.

 

[offthelip]

One easy way to do this is to use VBA to rewrite the equation you need automaticaly when the Date range ( which is also the names of the worksheets) changes.
You haven't said which are the two cells which contain the date ranages I have assumed it is Cells A1 and B1, Change all references to these two cells to your actual cells.
Also you haven't said where you want the equation so I have put it in D3, obviously change this to what you want
Put this sub in the worksheet change event code

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:B1")) Is Nothing Then
 Application.EnableEvents = False
    Range("D3:D3").Formula = "=SUM([DailyTracker.xlsx]" & Range("A1:A1") & ":" & Range("B1:B1") & "!$B$11)"
 Application.EnableEvents = True
End If
End Sub