Create a summary page

ctay20

New Member
Joined
Apr 14, 2011
Messages
13
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
That is fantastic! I hope someday that I understand what this is doing but it works!

Thank you!
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

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