Summary sheets Using Array

[Jmoz092]

I've been asked to provide a summary of payroll by department. I have timesheets (workbooks) for each employee, and a summary workbook that looks up data in each of those workbooks. Each worksheet in the Summary workbook is titled as the date of the pay period, and each employee's name is listed in rows. Column K has each employee's gross pay.

I've listed our departments on Sheet12 (named "Departments") as such:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Department
[/TD]
[TD]12-1-17[/TD]
[TD]12-15-17[/TD]
[TD]12-29-17[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Each Department has a an array of employees listed on Sheet11 (named "Reference") as such:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Dept A[/TD]
[TD]Dept B[/TD]
[TD]Dept C[/TD]
[TD]Dept D[/TD]
[TD]Dept E[/TD]
[TD]Dept F[/TD]
[/TR]
[TR]
[TD]employee1[/TD]
[TD]employee3[/TD]
[TD]employee5[/TD]
[TD]employee7[/TD]
[TD]employee9[/TD]
[TD]employee11[/TD]
[/TR]
[TR]
[TD]employee2[/TD]
[TD]employee4[/TD]
[TD]employee6[/TD]
[TD]employee8[/TD]
[TD]employee10[/TD]
[TD]employee12[/TD]
[/TR]
</tbody>[/TABLE]

Each Department is a named array of rows 2:101 (department name in row 1).

How can I have each department's gross pay displayed in the table on Sheet12 by pay period? Worksheet formulas or VBA solution is ok. I just need to future-proof it so that it can accommodate new employees.

 

[Jmoz092]

I've tried using the following formula in B2 of the first table in the previous post (Department A's total payroll for 12-1-17) but I'm only returning the first payroll value for the employee that is listed in department A on the reference sheet.

[COLOR=#000000][FONT=&quot][SIZE=3]{=SUMIF('12-01-17'!A15:A29,Dept A, '12-01-17'!K15:K29)}[/SIZE][/FONT][/COLOR]

 

[Simon4s]

I've been asked to provide a summary of payroll by department. I have timesheets (workbooks) for each employee, and a summary workbook that looks up data in each of those workbooks. Each worksheet in the Summary workbook is titled as the date of the pay period, and each employee's name is listed in rows. Column K has each employee's gross pay.

I've listed our departments on Sheet12 (named "Departments") as such:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Department[/TD]
[TD]12-1-17[/TD]
[TD]12-15-17[/TD]
[TD]12-29-17[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Each Department has a an array of employees listed on Sheet11 (named "Reference") as such:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Dept A[/TD]
[TD]Dept B[/TD]
[TD]Dept C[/TD]
[TD]Dept D[/TD]
[TD]Dept E[/TD]
[TD]Dept F[/TD]
[/TR]
[TR]
[TD]employee1[/TD]
[TD]employee3[/TD]
[TD]employee5[/TD]
[TD]employee7[/TD]
[TD]employee9[/TD]
[TD]employee11[/TD]
[/TR]
[TR]
[TD]employee2[/TD]
[TD]employee4[/TD]
[TD]employee6[/TD]
[TD]employee8[/TD]
[TD]employee10[/TD]
[TD]employee12[/TD]
[/TR]
</tbody>[/TABLE]

Each Department is a named array of rows 2:101 (department name in row 1).

How can I have each department's gross pay displayed in the table on Sheet12 by pay period? Worksheet formulas or VBA solution is ok. I just need to future-proof it so that it can accommodate new employees.

1. Make a new worksheet. call it "Names"
2. In A1, type in "period"
3. ** assuming that u have 5 periods for this comment: "Each worksheet in the Summary workbook is titled as the date of the pay period"
3. - Type the names of each worksheet from cells A2 to A6
4. Select a2:a6
5. define name
6. call it "period"
7. Go to the first of your 5 worksheets, select the entire table, define name, type in the title from worksheet Names A2
8. repeat for the remaining 4 worksheets.

9. Go to your summary worksheet that u are suppose to make
10. Click on any available cell (b1)
11. click data
12. click data validation
13. select List
14. type in "=period" without quotations (you have created a drop down list with your 5 pay periods)

Assuming your names are in summary!A1:A6
15. in your summary tab

Type in:
=Vlookup(A1,indirect(b1),COLUMN#,false)

This is what you want to do whether it is a sumif or a vlookup. You want to indirectly reference B1 <-- your dropdown list, aka "period" which is a list containing the names of your worksheets.

 

[Jmoz092]

thank you