Hello all,

First post over here, have been lurking on the board for a little over a month learning from all of you!

I am creating a workbook for tracking production hours used on construction projects, and am having some issues with my summary sheet. I need the summary sheet to look at the individual time sheets for the appropriate Phase Code, and sum all of the hours from that Phase Code across all of the time sheets. My issue is that I want this to be a variable number of time sheets available, so if the user needs to add sheets they can, and the formula still captures the new sheets. I have this all figured out by using a Named Range (i.e. Generate 5 time sheets, name them TS1-TS5, and create a list on the summary sheet and name the range "Timesheets".). This works for it's intended purpose, however since we have varying crew size, and potential for long duration on jobs, we need to have the flexibility of adding more time sheets to the workbook.

If I haven't lost you yet, here is what I envision the formula to look like: =SUMPRODUCT(SUMIF(INDIRECT("'"&TS1!:TS5!&"'!$C$13:$C$34"),B6,INDIRECT("'"&TS1!:TS5!&"'!$S$13:$S$34"))). This does not work, as if the INDIRECT statement does not know that I am referring to sheets TS1:TS5.

I have attached my workbook so you may see what I am working with. Please excuse the lack of formatting at the moment.
Sheet 'Instructions' has a button macro for generating new sheets. The named range for the SUMPRODUCT formula is also on this sheet.
Summary sheet is self explanatory. I have left cells D7 and E7 populated so that you may see what I have been trying/doing.

Thanks in advance!

JustinBook1.xlsm