Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help searching and summing across multilple worksheets
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help searching and summing across multilple worksheets
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$10 0"),"="&$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, D2:D100 ... (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 "Joe Tapestry" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help searching and summing across multilple worksheets
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. "Toppers" wrote: 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$10 0"),"="&$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, D2:D100 ... (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 "Joe Tapestry" wrote: 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help searching and summing across multilple worksheets
Yes!
"Toppers" wrote: 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? "Joe Tapestry" wrote: 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. "Toppers" wrote: 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$10 0"),"="&$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, D2:D100 ... (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 "Joe Tapestry" wrote: 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help searching and summing across multilple worksheets
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? "Joe Tapestry" wrote: 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. "Toppers" wrote: 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$10 0"),"="&$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, D2:D100 ... (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 "Joe Tapestry" wrote: 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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help searching and summing across multilple worksheets
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 "Joe Tapestry" wrote: Yes! "Toppers" wrote: 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? "Joe Tapestry" wrote: 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. "Toppers" wrote: 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$10 0"),"="&$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, D2:D100 ... (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 "Joe Tapestry" wrote: 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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help searching and summing across multilple worksheets
Thanks for your time on this, but we're way beyond my level of excel
expertise right now. Thanks anyway! "Toppers" wrote: 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 "Joe Tapestry" wrote: Yes! "Toppers" wrote: 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? "Joe Tapestry" wrote: 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. "Toppers" wrote: 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$10 0"),"="&$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, D2:D100 ... (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 "Joe Tapestry" wrote: 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? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help searching and summing across multilple worksheets
Joe,
If you want to send me a workbook, I'll add the code. ) "Joe Tapestry" wrote: Thanks for your time on this, but we're way beyond my level of excel expertise right now. Thanks anyway! "Toppers" wrote: 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 "Joe Tapestry" wrote: Yes! "Toppers" wrote: 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? "Joe Tapestry" wrote: 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. "Toppers" wrote: 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$10 0"),"="&$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, D2:D100 ... (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 "Joe Tapestry" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
searching for values and summing the corresponding values | Excel Worksheet Functions |