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:
[TABLE="width: 626"]
<tbody>[TR]
[TD]Monday[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]North America[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Claims Worked[/TD]
[TD]Claims Closed[/TD]
[TD]Closure %[/TD]
[TD]HRs Spent[/TD]
[TD]Avg Worked Per HR[/TD]
[TD]Avg Closed Per HR[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]75[/TD]
[TD]72[/TD]
[TD]96%[/TD]
[TD]7[/TD]
[TD]10.714286[/TD]
[TD]10.286[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]62[/TD]
[TD]60[/TD]
[TD]97%[/TD]
[TD]7[/TD]
[TD]8.8571429[/TD]
[TD]8.5714[/TD]
[/TR]
[TR]
[TD]Tim[/TD]
[TD]81[/TD]
[TD]70[/TD]
[TD]86%[/TD]
[TD]8[/TD]
[TD]10.125[/TD]
[TD]8.75[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]37[/TD]
[TD]37[/TD]
[TD]100%[/TD]
[TD]5[/TD]
[TD]7.4[/TD]
[TD]7.4[/TD]
[/TR]
[TR]
[TD]Josh[/TD]
[TD]52[/TD]
[TD]50[/TD]
[TD]96%[/TD]
[TD]7[/TD]
[TD]7.4285714[/TD]
[TD]7.1429[/TD]
[/TR]
[TR]
[TD]Ryan[/TD]
[TD]80[/TD]
[TD]74[/TD]
[TD]93%[/TD]
[TD]8[/TD]
[TD]10[/TD]
[TD]9.25[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD]82[/TD]
[TD]75[/TD]
[TD]91%[/TD]
[TD]7.5[/TD]
[TD]10.933333[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]37[/TD]
[TD]37[/TD]
[TD]100%[/TD]
[TD]5[/TD]
[TD]7.4[/TD]
[TD]7.4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]506[/TD]
[TD]475[/TD]
[TD]93.87%[/TD]
[TD]54.5[/TD]
[TD]9.2844037[/TD]
[TD]8.7156[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Incoming claims[/TD]
[TD]564[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Difference[/TD]
[TD]-58[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]North America[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Claims Worked[/TD]
[TD]Claims Closed[/TD]
[TD]Closure %[/TD]
[TD]HRs Spent[/TD]
[TD]Avg Worked Per HR[/TD]
[TD]Avg Closed Per HR[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]90[/TD]
[TD]85[/TD]
[TD]94%[/TD]
[TD]7[/TD]
[TD]12.857143[/TD]
[TD]12.143[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]82[/TD]
[TD]80[/TD]
[TD]98%[/TD]
[TD]7.5[/TD]
[TD]10.933333[/TD]
[TD]10.667[/TD]
[/TR]
[TR]
[TD]Tim[/TD]
[TD]75[/TD]
[TD]68[/TD]
[TD]91%[/TD]
[TD]8[/TD]
[TD]9.375[/TD]
[TD]8.5[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]54[/TD]
[TD]50[/TD]
[TD]93%[/TD]
[TD]7[/TD]
[TD]7.7142857[/TD]
[TD]7.1429[/TD]
[/TR]
[TR]
[TD]Josh[/TD]
[TD]62[/TD]
[TD]60[/TD]
[TD]97%[/TD]
[TD]8[/TD]
[TD]7.75[/TD]
[TD]7.5[/TD]
[/TR]
[TR]
[TD]Ryan[/TD]
[TD]57[/TD]
[TD]54[/TD]
[TD]95%[/TD]
[TD]8[/TD]
[TD]7.125[/TD]
[TD]6.75[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD]49[/TD]
[TD]46[/TD]
[TD]94%[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]6.5714[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]26[/TD]
[TD]25[/TD]
[TD]96%[/TD]
[TD]5[/TD]
[TD]5.2[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]495[/TD]
[TD]468[/TD]
[TD]94.55%[/TD]
[TD]57.5[/TD]
[TD]8.6086957[/TD]
[TD]8.1391[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Incoming claims[/TD]
[TD]253[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Difference[/TD]
[TD]242[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
With a section at the bottom of each worksheet with the Average stats for that week:
[TABLE="width: 585"]
<tbody>[TR]
[TD]Weekly Average[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]North America[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Claims Worked[/TD]
[TD]Claims Closed[/TD]
[TD]Closure %[/TD]
[TD]Weekly Average HRs Spent Per Day[/TD]
[TD]Weekly Avg Worked Per HR Per Day[/TD]
[TD]Weekly Avg Closed Per HR Per Day[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]68.75[/TD]
[TD]64.75[/TD]
[TD]94%[/TD]
[TD]6.625[/TD]
[TD]10.377358[/TD]
[TD]9.7736[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]72[/TD]
[TD]70[/TD]
[TD]97%[/TD]
[TD]7.25[/TD]
[TD]9.9310345[/TD]
[TD]9.6552[/TD]
[/TR]
[TR]
[TD]Tim[/TD]
[TD]65.2[/TD]
[TD]58[/TD]
[TD]89%[/TD]
[TD]7.8[/TD]
[TD]8.3589744[/TD]
[TD]7.4359[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]45.5[/TD]
[TD]43.5[/TD]
[TD]96%[/TD]
[TD]6[/TD]
[TD]7.5833333[/TD]
[TD]7.25[/TD]
[/TR]
[TR]
[TD]Josh[/TD]
[TD]57[/TD]
[TD]55[/TD]
[TD]96%[/TD]
[TD]7.5[/TD]
[TD]7.6[/TD]
[TD]7.3333[/TD]
[/TR]
[TR]
[TD]Ryan[/TD]
[TD]68.5[/TD]
[TD]64[/TD]
[TD]93%[/TD]
[TD]8[/TD]
[TD]8.5625[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD]65.5[/TD]
[TD]60.5[/TD]
[TD]92%[/TD]
[TD]7.25[/TD]
[TD]9.0344828[/TD]
[TD]8.3448[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]32.6[/TD]
[TD]31.2[/TD]
[TD]96%[/TD]
[TD]5[/TD]
[TD]6.52[/TD]
[TD]6.24[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]475.05[/TD]
[TD]446.95[/TD]
[TD]94.08%[/TD]
[TD]55.425[/TD]
[TD]8.5710419[/TD]
[TD]8.0641[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Average[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Incoming claims[/TD]
[TD]349.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Difference[/TD]
[TD]125.45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Week[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Incoming[/TD]
[TD]1748[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Closed[/TD]
[TD]1291[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Worked[/TD]
[TD]1381[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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.
[TABLE="width: 626"]
<tbody>[TR]
[TD]Monday[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]North America[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Claims Worked[/TD]
[TD]Claims Closed[/TD]
[TD]Closure %[/TD]
[TD]HRs Spent[/TD]
[TD]Avg Worked Per HR[/TD]
[TD]Avg Closed Per HR[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]75[/TD]
[TD]72[/TD]
[TD]96%[/TD]
[TD]7[/TD]
[TD]10.714286[/TD]
[TD]10.286[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]62[/TD]
[TD]60[/TD]
[TD]97%[/TD]
[TD]7[/TD]
[TD]8.8571429[/TD]
[TD]8.5714[/TD]
[/TR]
[TR]
[TD]Tim[/TD]
[TD]81[/TD]
[TD]70[/TD]
[TD]86%[/TD]
[TD]8[/TD]
[TD]10.125[/TD]
[TD]8.75[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]37[/TD]
[TD]37[/TD]
[TD]100%[/TD]
[TD]5[/TD]
[TD]7.4[/TD]
[TD]7.4[/TD]
[/TR]
[TR]
[TD]Josh[/TD]
[TD]52[/TD]
[TD]50[/TD]
[TD]96%[/TD]
[TD]7[/TD]
[TD]7.4285714[/TD]
[TD]7.1429[/TD]
[/TR]
[TR]
[TD]Ryan[/TD]
[TD]80[/TD]
[TD]74[/TD]
[TD]93%[/TD]
[TD]8[/TD]
[TD]10[/TD]
[TD]9.25[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD]82[/TD]
[TD]75[/TD]
[TD]91%[/TD]
[TD]7.5[/TD]
[TD]10.933333[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]37[/TD]
[TD]37[/TD]
[TD]100%[/TD]
[TD]5[/TD]
[TD]7.4[/TD]
[TD]7.4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]506[/TD]
[TD]475[/TD]
[TD]93.87%[/TD]
[TD]54.5[/TD]
[TD]9.2844037[/TD]
[TD]8.7156[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Incoming claims[/TD]
[TD]564[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Difference[/TD]
[TD]-58[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]North America[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Claims Worked[/TD]
[TD]Claims Closed[/TD]
[TD]Closure %[/TD]
[TD]HRs Spent[/TD]
[TD]Avg Worked Per HR[/TD]
[TD]Avg Closed Per HR[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]90[/TD]
[TD]85[/TD]
[TD]94%[/TD]
[TD]7[/TD]
[TD]12.857143[/TD]
[TD]12.143[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]82[/TD]
[TD]80[/TD]
[TD]98%[/TD]
[TD]7.5[/TD]
[TD]10.933333[/TD]
[TD]10.667[/TD]
[/TR]
[TR]
[TD]Tim[/TD]
[TD]75[/TD]
[TD]68[/TD]
[TD]91%[/TD]
[TD]8[/TD]
[TD]9.375[/TD]
[TD]8.5[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]54[/TD]
[TD]50[/TD]
[TD]93%[/TD]
[TD]7[/TD]
[TD]7.7142857[/TD]
[TD]7.1429[/TD]
[/TR]
[TR]
[TD]Josh[/TD]
[TD]62[/TD]
[TD]60[/TD]
[TD]97%[/TD]
[TD]8[/TD]
[TD]7.75[/TD]
[TD]7.5[/TD]
[/TR]
[TR]
[TD]Ryan[/TD]
[TD]57[/TD]
[TD]54[/TD]
[TD]95%[/TD]
[TD]8[/TD]
[TD]7.125[/TD]
[TD]6.75[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD]49[/TD]
[TD]46[/TD]
[TD]94%[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]6.5714[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]26[/TD]
[TD]25[/TD]
[TD]96%[/TD]
[TD]5[/TD]
[TD]5.2[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]495[/TD]
[TD]468[/TD]
[TD]94.55%[/TD]
[TD]57.5[/TD]
[TD]8.6086957[/TD]
[TD]8.1391[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Incoming claims[/TD]
[TD]253[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Difference[/TD]
[TD]242[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
With a section at the bottom of each worksheet with the Average stats for that week:
[TABLE="width: 585"]
<tbody>[TR]
[TD]Weekly Average[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]North America[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Claims Worked[/TD]
[TD]Claims Closed[/TD]
[TD]Closure %[/TD]
[TD]Weekly Average HRs Spent Per Day[/TD]
[TD]Weekly Avg Worked Per HR Per Day[/TD]
[TD]Weekly Avg Closed Per HR Per Day[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]68.75[/TD]
[TD]64.75[/TD]
[TD]94%[/TD]
[TD]6.625[/TD]
[TD]10.377358[/TD]
[TD]9.7736[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]72[/TD]
[TD]70[/TD]
[TD]97%[/TD]
[TD]7.25[/TD]
[TD]9.9310345[/TD]
[TD]9.6552[/TD]
[/TR]
[TR]
[TD]Tim[/TD]
[TD]65.2[/TD]
[TD]58[/TD]
[TD]89%[/TD]
[TD]7.8[/TD]
[TD]8.3589744[/TD]
[TD]7.4359[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]45.5[/TD]
[TD]43.5[/TD]
[TD]96%[/TD]
[TD]6[/TD]
[TD]7.5833333[/TD]
[TD]7.25[/TD]
[/TR]
[TR]
[TD]Josh[/TD]
[TD]57[/TD]
[TD]55[/TD]
[TD]96%[/TD]
[TD]7.5[/TD]
[TD]7.6[/TD]
[TD]7.3333[/TD]
[/TR]
[TR]
[TD]Ryan[/TD]
[TD]68.5[/TD]
[TD]64[/TD]
[TD]93%[/TD]
[TD]8[/TD]
[TD]8.5625[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD]65.5[/TD]
[TD]60.5[/TD]
[TD]92%[/TD]
[TD]7.25[/TD]
[TD]9.0344828[/TD]
[TD]8.3448[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]32.6[/TD]
[TD]31.2[/TD]
[TD]96%[/TD]
[TD]5[/TD]
[TD]6.52[/TD]
[TD]6.24[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]475.05[/TD]
[TD]446.95[/TD]
[TD]94.08%[/TD]
[TD]55.425[/TD]
[TD]8.5710419[/TD]
[TD]8.0641[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Average[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Incoming claims[/TD]
[TD]349.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Difference[/TD]
[TD]125.45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Week[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Incoming[/TD]
[TD]1748[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Closed[/TD]
[TD]1291[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Worked[/TD]
[TD]1381[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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.
