Help searching and summing across multilple worksheets

[Guest]

I have several worksheets with project time data. Each worksheet has people
down the left (different lists in each worksheet), months across the top and
days worked filling in the worksheet. In a separate worksheet I want to sum
the total days worked for each person in a particular month. That is, I have
to search each worksheet for a person & month and sum the total days worked
on the various projects. Is there a easy way to accomplish this?

 

[Guest]

Joe,
Assuming your project sheets/summary sheet look something like:
A B C D
E F
Name Jan Feb Mar Apr May
John Smith 57 41 77 57 41
Sam White 57 60 51 57 93
David Brown 44 49 26 44 35

Then in your summary sheet you could put the following formula:

=SUMPRODUCT(SUMIF(INDIRECT("'"&WSLST&"'!$A$2:$A$100"),"="&$A2,INDIRECT("'"&WSLST&"'!B2:B100")))

The above would be placed in B2. For cells C2, D2 etc you would need to
change B2:B100 to C2:C100, D2100 ... (and change the 100 to your maximum
number of names on a sheet).

Once you have defined the first row (for first name in Summary) just copy
formulae down.

WSLST is a named range containing the names of your project worksheets and
should be placed soewhere on your summary sheet.

As names are added/deleted just add/delete from the summary sheet and copy
formulae as required.

HTH

 

[Guest]

Thank you for the reply, HTH. A problem is that the sheets can have different
months/dates across the top, so Jan06 might be in column B in one sheet,
column E in another sheet and not exist at all on another sheet. I need to
be able to search and sum all the John Smith 57s for Jan06 in the various
worksheets. Ugh.

 

[Guest]

Yes!

Joe,
Does your Summary sheet contain all the possible valid dates and
are they in chronological order so these can be used as the reference (to be
searched for) dates?

 

[Guest]

Joe,
Does your Summary sheet contain all the possible valid dates and
are they in chronological order so these can be used as the reference (to be
searched for) dates?

 

[Guest]

Joe,
Try this:

In summary and project sheets..

<Names> in column A
<Dates> in row 1, starting column B

Matches <dates> and <names> in project sheets against summary sheet and
increments accordingly.

HTH

Sub Summary()

Dim dteRng As Range, NameRng As Range

With Worksheets("sheet3") '<=== Summary Sheet ... change name
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
Set NameRng = .Range("a1:a" & lastrow)
Set dteRng = .Range("a1:iv1")
.Range("b2.Iv200").ClearContents ' <=== clear cells ... change as
required
End With
For Each sh In Worksheets
If sh.Name <> "Sheet3" Then
With Worksheets(sh.Name)
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
lastcol = .Cells(1, Columns.Count).End(xlToLeft).Column
For c = 2 To lastcol
For r = 2 To lastrow
Row = Application.Match(.Cells(r, "A"), NameRng, 0)
col = Application.Match(.Cells(1, c), dteRng, 0)
If Not IsError(Row) And Not IsError(col) Then
Worksheets("sheet3").Cells(Row, col) =
Worksheets("sheet3").Cells(Row, col) + _
.Cells(r, c)
End If
Next r
Next c
End With
End If
Next sh

End Sub

 

[Guest]

Thanks for your time on this, but we're way beyond my level of excel
expertise right now. Thanks anyway!

 

[Guest]

Joe,
If you want to send me a workbook, I'll add the code.
([email protected])