SUMPRODUCT/SUMIF Issues

**Jskrilla** · 05-29-2014, 03:40 PM

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

**p24leclerc** · 05-29-2014, 10:08 PM

Hi Justin,
Welcome to this forum.
May I ask you some questions?
Would it be possible for you to have only one sheet to track production time? You would need more columns to detail every entry but then you'll be able to use pivot table to work out your summary sheet for you.

If you stick with your actual plan, one thing you could do is to make your INDEX named range a dynamic one and to add new sheet's name at the end of Index when someone hit the generate new sheet button. This will keep your old formula working forever.

**Jskrilla** · 05-30-2014, 03:48 AM

Thanks for the reply Pierre!

I can't have a single sheet, as each sheet will be used as a timesheet for each employee and sent to our accounting department. I like your idea for the dynamic range, I forgot that Excel had that feature; the next question is what would be the correct macro or VBA coding to apply the additional sheets to the dynamic range? I also see the need to have the worksheet names (on the tab) auto-populate to the dynamic range, so that if a user changes the text on the tab, the dynamic range stays intact.

Making progress, progress is good! Thanks for everyone that has looked, and thank you Pierre!

**p24leclerc** · 05-30-2014, 09:46 PM

See attached workbook for a working solution of a dynamic range "Index" along with a macro to update it only when sheet "Summary" is activated ( or selected)
here is the macro:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim Ws As Worksheet
Dim i As Integer
'Change Dynamic range "Index" only if Summary sheet is activated
If Sh.Name = "Summary" Then
   'Disable the EVENTS procedure so this one is not calling itself over and over
   Application.EnableEvents = False
   i = 0
   'Scan each sheets in the workbook
   For Each Ws In ActiveWorkbook.Worksheets
      'Except for sheets "Instructions" and "Summary"
      'Put worksheet name in dynamic range "Index"
      If Ws.Name <> "Summary" And Ws.Name <> "Instructions" Then
         Sheets("Instructions").Range("P7").Offset(i) = Ws.Name
         i = i + 1
      End If
   Next
   Application.EnableEvents = True
End If
End Sub

We don't want this macro to be activated too often and it makes sense to update the "Index" dynamic range only when you select sheet "Summary" as when you are in any other sheet, results of the formulas in that sheet is not important to us.
Hope this helps
Regards