Jul 15 2019 11:58 PM - edited Jul 16 2019 12:00 AM
Hello all,
New to the community, and unfortunately not able to find a thread that answers my problem. Hope someone can assist or suggest a better solution.
I am creating a TOTALS workbook that shows the hours worked by 100+ employees each day Mon-Sun for reporting to client. The totals needs to be pulled over from 7 other daily workbooks where hours are manually entered by admin. I believe the solution could be to utilise VLOOKUP and SUMIF but not sure how to execute.
Filename for workbooks are:
20190715 Monday DWS
20190716 Tuesday DWS etc
Up to 20190721 Sunday DWS
The DWS workbooks are identical in layout. Sheet1 is a summary table; personnel names in A10:A110, 18 columns (D to U) of individual job codes and column V is total hours for the personnel. Sheet2 through to Sheet18 are for the individual job codes and have just 2 columns for NAME and HOURS copied across from the summary sheet using:
=IF('MSP Day Works Sheet '!$A10 <> "", 'MSP Day Works Sheet '!$A10, "") (for NAME)
=IF('MSP Day Works Sheet '!$D10 <> "", 'MSP Day Works Sheet '!$D10, "") (for HOURS)
The layout for the totals workbook is personnel names A5:A105 and dates for Mon-Sun (ie 16/07/19 to 21/07/19) in B:H.
As an example, I need to show all the hours "ANDREW, Dave" worked over the 7 day period, copy those hours over from different books (but with same sheet name, and cell range).
Book: 20190716 Tuesday DWS, 20190717 Wednesday DWS, etc
Sheet: MSP Day Works Sheet
Name = A10
Total hrs = V10
Any suggestion would be much welcomed,
Fred
Jul 17 2019 07:31 AM
Hello @Frederic0,
I would suggest using the pivot table wizard to consolidate multiple worksheets/workbooks:
Once you create your pivot table, you can use the vlookup function to return the grand total for each worker into your master worksheet.
If you would like more help, please let me know!
PReagan