Home

VLOOKUP and SUMIF across multiple workbooks

%3CLINGO-SUB%20id%3D%22lingo-sub-756251%22%20slang%3D%22en-US%22%3EVLOOKUP%20and%20SUMIF%20across%20multiple%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-756251%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENew%20to%20the%20community%2C%20and%20unfortunately%20not%20able%20to%20find%20a%20thread%20that%20answers%20my%20problem.%20Hope%20someone%20can%20assist%20or%20suggest%20a%20better%20solution.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20creating%20a%20TOTALS%20workbook%20that%20shows%20the%20hours%20worked%20by%20100%2B%20employees%20each%20day%20Mon-Sun%20for%20reporting%20to%20client.%20The%20totals%20needs%20to%20be%20pulled%20over%20from%207%20other%20daily%20workbooks%20where%20hours%20are%20manually%20entered%20by%20admin.%26nbsp%3BI%20believe%20the%20solution%20could%20be%20to%20utilise%20VLOOKUP%20and%20SUMIF%20but%20not%20sure%20how%20to%20execute.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFilename%20for%20workbooks%20are%3A%3C%2FP%3E%3CP%3E20190715%20Monday%20DWS%3C%2FP%3E%3CP%3E20190716%20Tuesday%20DWS%20etc%3C%2FP%3E%3CP%3EUp%20to%2020190721%20Sunday%20DWS%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20DWS%20workbooks%20are%20identical%20in%20layout.%20Sheet1%20is%20a%20summary%20table%3B%20personnel%20names%20in%20A10%3AA110%2C%2018%20columns%20(D%20to%20U)%20of%20individual%20job%20codes%20and%20column%20V%20is%20total%20hours%20for%20the%20personnel.%20Sheet2%20through%20to%20Sheet18%20are%20for%20the%20individual%20job%20codes%20and%20have%20just%202%20columns%20for%20NAME%20and%20HOURS%20copied%20across%20from%20the%20summary%20sheet%20using%3A%3C%2FP%3E%3CP%3E%3CEM%3E%3DIF('MSP%20Day%20Works%20Sheet%20'!%24A10%20%26lt%3B%26gt%3B%20%22%22%2C%20'MSP%20Day%20Works%20Sheet%20'!%24A10%2C%20%22%22)%3C%2FEM%3E(for%20NAME)%3C%2FP%3E%3CP%3E%3CEM%3E%3DIF('MSP%20Day%20Works%20Sheet%20'!%24D10%20%26lt%3B%26gt%3B%20%22%22%2C%20'MSP%20Day%20Works%20Sheet%20'!%24D10%2C%20%22%22)%3C%2FEM%3E(for%20HOURS)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20layout%20for%20the%20totals%20workbook%20is%20personnel%20names%20A5%3AA105%20and%20dates%20for%20Mon-Sun%20(ie%2016%2F07%2F19%20to%2021%2F07%2F19)%20in%20B%3AH.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20an%20example%2C%20I%20need%20to%20show%20all%20the%20hours%20%22ANDREW%2C%20Dave%22%20worked%20over%20the%207%20day%20period%2C%20copy%20those%20hours%20over%20from%20different%20books%20(but%20with%20same%20sheet%20name%2C%20and%20cell%20range).%3C%2FP%3E%3CP%3EBook%3A%26nbsp%3B20190716%20Tuesday%20DWS%2C%26nbsp%3B20190717%20Wednesday%20DWS%2C%26nbsp%3Betc%3C%2FP%3E%3CP%3ESheet%3A%20MSP%20Day%20Works%20Sheet%3C%2FP%3E%3CP%3EName%20%3D%20A10%3C%2FP%3E%3CP%3ETotal%20hrs%20%3D%20V10%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20suggestion%20would%20be%20much%20welcomed%2C%3C%2FP%3E%3CP%3EFred%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-756251%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-759139%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20and%20SUMIF%20across%20multiple%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-759139%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F376847%22%20target%3D%22_blank%22%3E%40Frederic0%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20suggest%20using%20the%20pivot%20table%20wizard%20to%20consolidate%20multiple%20worksheets%2Fworkbooks%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fconsolidate-multiple-worksheets-into-one-pivottable-3ae257d2-ca94-49ff-a481-e9fc8adeeeb5%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fconsolidate-multiple-worksheets-into-one-pivottable-3ae257d2-ca94-49ff-a481-e9fc8adeeeb5%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnce%20you%20create%20your%20pivot%20table%2C%20you%20can%20use%20the%20vlookup%20function%20to%20return%20the%20grand%20total%20for%20each%20worker%20into%20your%20master%20worksheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20would%20like%20more%20help%2C%20please%20let%20me%20know!%3C%2FP%3E%3CP%3EPReagan%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frederic0
Occasional Visitor

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

 

 

1 Reply

Hello @Frederic0,

 

I would suggest using the pivot table wizard to consolidate multiple worksheets/workbooks:

https://support.office.com/en-us/article/consolidate-multiple-worksheets-into-one-pivottable-3ae257d...

 

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 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies