Jun 08 2023 10:35 AM
Good evening/morning,
I could do with some help on the example workbook I have added. What I am trying to acheive is this:-
Include all of the relevant invoice numbers from sheet 2, onto sheet one if the employee number and period number matches on both worksheets. I want this to work and include all of the hours for the 2 tests on sheet 2, matching the total hours worked on sheet 1. If the total hours on sheet 1 does not match the breakdown on sheet 2 (providing the emp number and period number also match), then "investigate".
So, for example, employee 1001 on period 1 should have invoice numbers 2001 & 2007 returned.
My brain is too fried to work this out tonight.
Many thanks in advance
Jun 08 2023 11:22 AM
=IF(SUMIFS(Sheet2!$C$2:$C$10,Sheet2!$A$2:$A$10,Sheet1!$A2,Sheet2!$B$2:$B$10,Sheet1!$B2)<>Sheet1!$C2,"Investigate",IFERROR(INDEX(Sheet2!$D$2:$D$10,SMALL(IF((Sheet2!$A$2:$A$10=Sheet1!$A2)*(Sheet2!$B$2:$B$10=Sheet1!$B2),ROW(Sheet2!$D$1:$D$9)),COLUMN(Sheet1!A1))),""))
You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. The formula is in cell D2 and filled across range D2:H5.
Jun 08 2023 11:40 AM
Jun 08 2023 01:07 PM
Jun 08 2023 01:09 PM
Jun 08 2023 01:23 PM
There is this thing called web search.
https://www.google.com/search?q=add+data+model+pivot+table
https://www.google.com/search?q=add+text+to+values+area+of+a+pivot+table
Jun 08 2023 01:58 PM
Jun 08 2023 02:23 PM
The web search gives you many step by step tutorials either as text or as video.
It's all there and I could not explain it better than those experts.
And regarding your error: Your pivot table needs more rows/columns and there are not enough blank rows/columns between your pivot tables.
Either add more blank rows/columns between your pivot tables or move one of the pivot tables onto another sheet.
https://www.google.com/search?q=a+table+can%27%27t+overlap+another+table
https://www.google.com/search?q=move+a+pivot+table+onto+another+sheet
Jun 08 2023 04:30 PM
Jun 08 2023 06:02 PM
Jun 09 2023 05:25 AM - edited Jun 09 2023 05:26 AM
@peiyezhu
You make a very valid point. Please now see attached updated sample workbook.
Many thanks in advance
Jun 10 2023 01:06 AM
Jun 10 2023 03:44 AM
Jun 10 2023 02:13 PM
Jun 10 2023 02:56 PM
=IF(SUMIFS(Sheet2!$C$2:$C$12,Sheet2!$A$2:$A$12,Sheet1!$A2,Sheet2!$B$2:$B$12,Sheet1!$B2,Sheet2!$E$2:$E$12,Sheet1!$H2,Sheet2!$E$2:$E$12,Sheet1!$H2)<>Sheet1!$C2,"Investigate",IFERROR(INDEX(Sheet2!$D$2:$D$12,SMALL(IF((Sheet2!$A$2:$A$12=Sheet1!$A2)*(Sheet2!$B$2:$B$12=Sheet1!$B2)*(Sheet2!$E$2:$E$12=Sheet1!$H2),ROW(Sheet2!$D$1:$D$11)),COLUMN(Sheet1!A1))),""))
With an additional criteria such as weeks you can distinguish employee numbers and periods. Then you can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.