AverageIF across tables on Multiple sheets; perferably with a reference to specific names

UmraTiwil

New Member
Joined
Jul 12, 2015
Messages
2
I have been tasked with creating a large workbook to track productivity for a department across all of the weekday for the year. It must also include a sheet for the year-to-date average production for each member of the department. The way this was done last year by using a new worksheet for each week, i.e. "Week 1," "Week 2," etc, that looked like this:

Monday
North America
NameClaims WorkedClaims ClosedClosure %HRs SpentAvg Worked Per HRAvg Closed Per HR
Bob757296%710.71428610.286
John626097%78.85714298.5714
Tim817086%810.1258.75
Steve3737100%57.47.4
Josh525096%77.42857147.1429
Ryan807493%8109.25
Sue827591%7.510.93333310
Mary3737100%57.47.4
Total50647593.87%54.59.28440378.7156
Incoming claims564
Difference-58
Tuesday
North America
NameClaims WorkedClaims ClosedClosure %HRs SpentAvg Worked Per HRAvg Closed Per HR
Bob908594%712.85714312.143
John828098%7.510.93333310.667
Tim756891%89.3758.5
Steve545093%77.71428577.1429
Josh626097%87.757.5
Ryan575495%87.1256.75
Sue494694%776.5714
Mary262596%55.25
Total49546894.55%57.58.60869578.1391
Incoming claims253
Difference242

<tbody>
</tbody>


With a section at the bottom of each worksheet with the Average stats for that week:

Weekly Average
North America
NameClaims WorkedClaims ClosedClosure %Weekly Average HRs Spent Per DayWeekly Avg Worked Per HR Per DayWeekly Avg Closed Per HR Per Day
Bob68.7564.7594%6.62510.3773589.7736
John727097%7.259.93103459.6552
Tim65.25889%7.88.35897447.4359
Steve45.543.596%67.58333337.25
Josh575596%7.57.67.3333
Ryan68.56493%88.56258
Sue65.560.592%7.259.03448288.3448
Mary32.631.296%56.526.24
Total475.05446.9594.08%55.4258.57104198.0641
Average
Incoming claims349.6
Difference125.45
Week
Total Incoming1748
Total Closed1291
Total Worked1381

<tbody>
</tbody>


On the YTD average page we used =Average for much of the data, but there were some cells where, in order to avoid skewing data where 0's were populating, we had to use formulas like this one:

=SUM('Week 1:Week 52'!B22)/INDEX(FREQUENCY('Week 1:Week 52'!B22,0),2

Although this worked, we ran into some issues as the department members changed throughout the year. As new members joined the departments, I had to add new rows for each day, which would then throw off the formula references, because the cells for each person changed. There was also a big problem when another user took over tracking the information for a few months while I was on a different project and he changed the order of the employees. This caused the YTD data to be invalid as it was combining different people's stats.

Going into next year, I thought it might be better to use tables on each worksheet. I could then use a macro that would reference the employees name across each worksheet and average the information on the YTD sheet. If it referenced the name, then the order would no longer matter. The problem is I am very new to VBA, and though I've done some research on it, I'm having some issues with getting it to reference by name.

I was wondering if it would be possible to adapt the SUMIF3D2 coding that jbeaucaire posted on this thread:

http://www.mrexcel.com/forum/excel-...teria-differnt-rows-across-multiple-tabs.html

Maybe a way to create a sort of AverageIF3D? I don't want to spend many hours trying to adapt this if it isn't going to work out in the end. Does anyone have any suggestions or input on this train of thought? Or better yet, has anyone created a macro that might work for my needs?

Any help with the problem to this point is greatly appreciated. The additional complication I'm thinking about, assuming I can get past the issues above, would be to try to make the YTD tables more Dynamic. For instance, lets say a new employee, Sam joins the group in Week 32. Is there a way that the YTD table would automatically add a new row for him based on his row being added to the tables in Week32? and then perform the required functions for his data starting with that week moving forward?

I know this is a lot and I don't expect anyone to spend hours and hours trying to work though it all, so any input or experience with this would be great.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top