Home

gather data in different worksheets

%3CLINGO-SUB%20id%3D%22lingo-sub-401173%22%20slang%3D%22en-US%22%3Egather%20data%20in%20different%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-401173%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20attached%20a%20file%20to%20make%20it%20easier%20to%20explain%20and%20hopefully%20to%20find%20a%20solution.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20file%20there%20is%20a%20tab%20individual%20performance%20and%20working%20hours.%20I%20would%20like%20to%20gather%20from%20the%20tab%20working%20hour%20in%20the%20tab%20individual%20performance%20the%20amount%20of%20hours%20worked%20in%20that%20week%20per%20person.%20how%20can%20I%20do%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Epassword%20of%20file%20is%201234%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-401173%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-401233%22%20slang%3D%22en-US%22%3ERe%3A%20gather%20data%20in%20different%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-401233%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F135331%22%20target%3D%22_blank%22%3E%40Ramon%20Haagen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20prepared%20two%20files%20for%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EV2%20file%3A%20using%20formula%20%22AVERAGEIFS%22%20to%20do%20the%20averaging.%20But%20it%20requires%20that%20the%20columns%20for%20person%20are%20correspondence.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EV3%20file%3A%20change%20the%20structure%20of%20the%20data%20and%20then%20using%20a%20pivot%20table%20to%20demonstrate%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-401214%22%20slang%3D%22en-US%22%3ERe%3A%20gather%20data%20in%20different%20worksheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-401214%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F135331%22%20target%3D%22_blank%22%3E%40Ramon%20Haagen%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DSUMIF(Working_Hours__2%5B%5BWEEK%5D%3A%5BWEEK%5D%5D%2C%24CB5%2CINDEX(Working_Hours__2%2C%2CMATCH(CC%244%2CWorking_Hours__2%5B%23Headers%5D%2C0)))%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Ramon Haagen
Occasional Contributor

Hello,

 

I attached a file to make it easier to explain and hopefully to find a solution.

 

In the file there is a tab individual performance and working hours. I would like to gather from the tab working hour in the tab individual performance the amount of hours worked in that week per person. how can I do this?

 

password of file is 1234

2 Replies

Hi @Ramon Haagen ,

 

It could be

=SUMIF(Working_Hours__2[[WEEK]:[WEEK]],$CB5,INDEX(Working_Hours__2,,MATCH(CC$4,Working_Hours__2[#Headers],0)))

 

@Ramon Haagen 

 

I prepared two files for you.

 

V2 file: using formula "AVERAGEIFS" to do the averaging. But it requires that the columns for person are correspondence. 

 

V3 file: change the structure of the data and then using a pivot table to demonstrate

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies