Create a summary page

[ctay20]

Hello, I am hoping someone can help me with a little VBA problem. I am trying to create a summary page of data spread across several worksheets in the same workbook. IE I have several employees data across multiple spreadsheets. Each employee may have a different amount of data depending on what they did that month. In row A there are column headers and total of the data is at the bottom of each column. I would like to create a worksheet in the same workbook that summarizes each employee with the numerical data from that month.

Here is my data: http://dl.dropbox.com/u/8242024/Employee%20Billable%20Hours%20February%202011.xlsx.

Here is an example of what I want it to look like when I'm done: http://dl.dropbox.com/u/8242024/Final Employee Billable Hours February 2011.xlsx

I appreciate any help you could give me! I tried to modify a script I found here: http://msdn.microsoft.com/en-us/library/cc793964.aspx but can't quite get it to do what I want...

Chris

 

[Smitty]

Welcome to the Board!

It would be a whole lot better if you post examples on the board. By putting something on Dropbox, or any other similar service, you only get one person looking at the question at a time. Whereas posting it here you have thousands of eyes on it, which one of the reason we discourage the former. Another limiter is that few people are going to download files from untrusted sources.

It's totally up to you of course, but if you want to post a shot of your sheet(s) follow the link in my sig.

 

[ctay20]

Thanks for the tip! Here is an example of my data:
Excel 2007<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">User Last Name</td><td style="font-weight: bold;;">User First Name</td><td style="font-weight: bold;;">User Department</td><td style="font-weight: bold;;">Project Number</td><td style="font-weight: bold;;">Project Name</td><td style="font-weight: bold;;">Task Name</td><td style="font-weight: bold;;">Date</td><td style="font-weight: bold;;">Service Description</td><td style="font-weight: bold;;">Hours</td><td style="font-weight: bold;;">Actual Billable Hours</td><td style="font-weight: bold;;">Actual Non Billable Hours</td><td style="font-weight: bold;;">Write off Hours</td><td style="font-weight: bold;;">Write off Total</td><td style="font-weight: bold;;">Write Off Reason</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style=";">Arroyo</td><td style=";">Janice</td><td style=";">Graphics and Art</td><td style=";">10-MVD-001</td><td style=";">September 10 Creative Website</td><td style=";">Round 2 Edits/Approvals</td><td style="text-align: right;;">2/3/2011</td><td style=";">Graphic Design, Layout, & Production</td><td style="text-align: right;;">1.5</td><td style="text-align: right;;">1.5</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">$0.00</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style=";">Arroyo</td><td style=";">Janice</td><td style=";">Graphics and Art</td><td style=";">11-BHP-004</td><td style=";">January 11 Creative BHP Billiton Community Ads</td><td style=";">Design Ad Spreads and Layouts</td><td style="text-align: right;;">2/18/2011</td><td style=";">Graphic Design, Layout, & Production</td><td style="text-align: right;;">1.5</td><td style="text-align: right;;">1.5</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">$0.00</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style=";">Arroyo</td><td style=";">Janice</td><td style=";">Graphics and Art</td><td style=";">11-BHP-004</td><td style=";">January 11 Creative BHP Billiton Community Ads</td><td style=";">Round 2 Edits/Approvals</td><td style="text-align: right;;">2/28/2011</td><td style=";">Graphic Design, Layout, & Production</td><td style="text-align: right;;">0.75</td><td style="text-align: right;;">0.75</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">$0.00</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style=";">Arroyo</td><td style=";">Janice</td><td style=";">Graphics and Art</td><td style=";">11-BHP-006</td><td style=";">February 11 Creative BHP Billiton Fact Sheets</td><td style=";">Round 4 Edits/Approvals</td><td style="text-align: right;;">2/23/2011</td><td style=";">Graphic Design, Layout, & Production</td><td style="text-align: right;;">1.25</td><td style="text-align: right;;">1.25</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">$0.00</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style=";">Arroyo</td><td style=";">Janice</td><td style=";">Graphics and Art</td><td style=";">11-CASA-003</td><td style=";">February 11 PR CASA (Pro Bono)</td><td style=";">Photo Research</td><td style="text-align: right;;">2/18/2011</td><td style=";">Graphic Design, Layout, & Production</td><td style="text-align: right;;">0.25</td><td style="text-align: right;;">0.25</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">$0.00</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">41</td><td style=";">Arroyo</td><td style=";">Janice</td><td style=";">Graphics and Art</td><td style=";">11-SAA-001</td><td style=";">February 11 Creative Seeker Aviation Collateral</td><td style=";">Design Spreads and Layouts</td><td style="text-align: right;;">2/1/2011</td><td style=";">Graphic Design, Layout, & Production</td><td style="text-align: right;;">2.5</td><td style="text-align: right;;">2.5</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">$0.00</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">42</td><td style=";">Arroyo</td><td style=";">Janice</td><td style=";">Graphics and Art</td><td style=";">11-SAA-001</td><td style=";">February 11 Creative Seeker Aviation Collateral</td><td style=";">Design Spreads and Layouts</td><td style="text-align: right;;">2/2/2011</td><td style=";">Graphic Design, Layout, & Production</td><td style="text-align: right;;">7.5</td><td style="text-align: right;;">7.5</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">$0.00</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">43</td><td style=";">Arroyo</td><td style=";">Janice</td><td style=";">Graphics and Art</td><td style=";">11-SAA-001</td><td style=";">February 11 Creative Seeker Aviation Collateral</td><td style=";">Design Spreads and Layouts</td><td style="text-align: right;;">2/3/2011</td><td style=";">Graphic Design, Layout, & Production</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">$0.00</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">44</td><td style=";">Arroyo</td><td style=";">Janice</td><td style=";">Graphics and Art</td><td style=";">11-SAA-001</td><td style=";">February 11 Creative Seeker Aviation Collateral</td><td style=";">Meeting</td><td style="text-align: right;;">2/1/2011</td><td style=";">Graphic Design, Layout, & Production</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">$0.00</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">45</td><td style=";">Arroyo</td><td style=";">Janice</td><td style=";">Graphics and Art</td><td style=";">11-STJO-003</td><td style=";">January 11 PR Events - St. Joseph Community Health</td><td style=";">Photo Research</td><td style="text-align: right;;">2/3/2011</td><td style=";">Graphic Design, Layout, & Production</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">$0.00</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">46</td><td style=";">Arroyo</td><td style=";">Janice</td><td style=";">Graphics and Art</td><td style=";">11-STJO-005</td><td style=";">February 11 St Joseph Community Health Media Relations</td><td style=";">Photo Research</td><td style="text-align: right;;">2/15/2011</td><td style=";">Graphic Design, Layout, & Production</td><td style="text-align: right;border-bottom: 1px solid black;;">0.5</td><td style="text-align: right;border-bottom: 1px solid black;;">0.5</td><td style="text-align: right;border-bottom: 1px solid black;;">0</td><td style="text-align: right;border-bottom: 1px solid black;;">0</td><td style="text-align: right;border-bottom: 1px solid black;;">$0.00</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">47</td><td style="font-weight: bold;text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">
</td><td style="font-weight: bold;text-align: right;;">
</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;">160</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;">50</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;">110</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;">3.5</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;;">$525.00</td><td style="font-weight: bold;text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">48</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">49</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="font-weight: bold;;">Percentage Billable Hours February 2011</td><td style="font-weight: bold;text-align: right;;">31.25%</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr></tbody></table>

Arroyo, Janice​

There are several worksheets in the workbook with similar data. Each worksheet may have the totals in a different row. I'm trying to create a summary sheet in the same workbook that looks like this:
Excel 2007<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Summary</td><td style="font-weight: bold;;">Hours</td><td style="font-weight: bold;;">Actual Billable Hours</td><td style="font-weight: bold;;">Actual Non Billable Hours</td><td style="font-weight: bold;;">Write off Hours</td><td style="font-weight: bold;;">Write off Total</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style=";">Arroyo, Janice</td><td style="text-align: right;;">160</td><td style="text-align: right;;">50</td><td style="text-align: right;;">110</td><td style="text-align: right;;">3.5</td><td style="text-align: right;;">$525.00</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style=";">Bhasin, Chandani</td><td style="text-align: right;;">30.75</td><td style="text-align: right;;">20.5</td><td style="text-align: right;;">10.25</td><td style="text-align: right;;">4.75</td><td style="text-align: right;;">$356.25</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style=";">Bitzer, Barry </td><td style="text-align: right;;">132.8</td><td style="text-align: right;;">47.25</td><td style="text-align: right;;">85.5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">$1,000.00</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style=";">Bowker, Paula</td><td style="text-align: right;;">156</td><td style="text-align: right;;">18.5</td><td style="text-align: right;;">137.5</td><td style="text-align: right;;">0</td><td style="text-align: right;;">$0.00</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style=";">Brunson, Laura</td><td style="text-align: right;;">160</td><td style="text-align: right;;">89.5</td><td style="text-align: right;;">70.5</td><td style="text-align: right;;">24.25</td><td style="text-align: right;;">$3,031.25</td></tr></tbody></table>

Summary​

Thank you for any help you can offer!

Chris

 

[Haseeb Avarakkan]

If sheet names are the same as users, like in Summary Col_A. Try in Summary B2, copy across & down.

=SUM(INDIRECT("'"&$A2&"'!"&ADDRESS(1,COLUMNS($B2:B2)+8)&":"&ADDRESS(10000,COLUMNS($B2:B2)+8)))/2

 

[ctay20]

That is fantastic! I hope someday that I understand what this is doing but it works!

Thank you!