Thread: Criteria based Running totals across multiple sheets

Hey all

I have a workbook which contains weekly timesheets with multiple job listings. The workbook will grow with sheets having Friday dates through the rest of the year. I have a named range (Dates) that contains all existing and future sheet names.

What I'd like to do, starting with "12-30" is create a formula in AG15 that would look in all sheets matching names in (Dates) for the value in B15 and SUM column AE. Same thing for AH regarding AF. Essentially, AE and AF are weekly totals where AG and AH would sum all the hours worked against a W.O. # from B:B through all worksheets in the workbok.

The latest sheet is always copied before "WeeklyDates" when a new week starts so it's OK if the running totals for prior weeks reflect hours worked after that week.

Hope that explains it.

As always guys.... Thanks and Happy New Year!!!!!

Bump. No response

Kvsrinivasamurthy I appreciate the response and those formulas would work fine unfortunately they would require manual manipulation every time a sheet was copied and named as a new day.

The ultimate goal is to have a formula that would look at any sheet in the workbook that exists and has a sheet name that matches any of the names in the dynamic range (dates).

I had a running version of this same workbook before using the formula

=SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(dates,"M-D")&"'!B15:B1000"),B15,INDIRECT("'"&TEXT(dates,"M-D")&"'!AE15:AE1000")))+SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(dates,"M-D")&"'!B15:B1000"),B15,INDIRECT("'"&TEXT(dates,"M-D")&"'!AF15:AF1000")))

but I can't get it to work when creating a workbook for the new year. I had not originally posted this formula due to performance issues I believe it was causing when the nuiber of sheets got high. I thought there would be a cleaner way of doing it.... different formula or code

Marked the issue as solved even though it wasn;t through the route that I wanted to go. After much toiling I dropped back to punt. I created another sheet title jobs where I will keep a running list of all WO #'s, descriptions and areas. I then created 52 colomns with SUMIF formula looking for each individual WO # in each weekly sheet, then totaled them per Staright time and OT in a 53rd and 54th colomns. From there VLOOKUPS worked on each individual sheet looking at the totals colomn on the Jobs sheet.... Way to much time typing today!