At the end of the answer, is a macro that you can run to generate /
update the summary sheet. It only needs to be run when you add,
remove, or rename a worksheet.
Prior to adding the macro, I recommend you format the cells on the
summary sheet to the way you want the entries to appear. The
formatting I used was:
Column A - General
Column B - Date (choose a format you want)
Columns C and D - Number (I used the default of 2 digits, but you
can use what you need)
I applied the formatting to 60 rows or so (to allow for growth). I do
not suggest using the "text" format - I found in that case that the
formula would be displayed instead of the result.
To add the macro to your worksheet, do the following steps.
 Open the worksheet (or select it if its already open).
 Use the menu
Tools -> Macro -> Visual Basic Editor
 In visual basic, a project window should appear in the upper left.
Make sure that your worksheet is selected. Then use the menu
Insert -> Module
a module (code) window should appear.
 Copy and paste the code at the end of the message into the code
window. Make any updates you find necessary (see explanation of the
At this point, you can "Quit and Return to Microsoft Excel". To run
the macro, use the menu
Tools -> Macro -> Macros...
and select the MakeSummary macro and run.
The macro takes just a moment to run on my system and the summary
sheet should show the current list of people / dates / hours. As
updates are made on the time card sheets, the data will carry
automatically to the summary sheet. The macro will also be saved with
If you haven't used macros before, be sure to change your macro
security level to "medium" to allow you to run the macro. You didn't
say which version of Excel you are using, here's a reference for Excel
2003 describing the issues:
Make a clarification request indicating your Excel version if you
can't find the setting on your version of Excel.
Explanation of the Macro
Let me describe the macro briefly and identify some places you may
want to make updates.
The macro starts by ensuring the SUMMARY sheet is selected - if you
change the name of that sheet, update the string in the sheet select
line. The macro then clears any existing values in the summary (in
case you remove worksheets). If you have more than 59 people, change
the range $A$2:$D$60 to match.
The loop has two indicies
J tracks the current row in the summary sheet
I tracks the current worksheet being processed
In this way, if you remove a name from the worksheet (w/o removing the
worksheet), it will suppress blank rows in the summary. There is also
a test to ignore the conversion table worksheet. If you have other
conditions to skip a sheet, use the two IF statements at the top of
the loop as a guide to adding a new condition.
In each loop, the name of the worksheet is used to generate the
formulas. This is why you need to rerun the macro if you rename a
worksheet (to update the sheet name).
Please make a clarification request if the answer is unclear or
incomplete. I will be glad to help you further as necessary. Good luck
with your work.
Macro to generate the summary page
' MakeSummary Macro
' Macro created 6/12/2006 by Maniac
' Clear the existing values (if any)
Range("$A$2:$D$60").Value = ""
' J tracks the row number on the summary page
' I tracks the sheet number being processed
J = 2
For I = 2 To Sheets.Count
A$ = Sheets(I).Name
' Don't process a sheet if its name is "Conversion Table"
' or if the name is blank.
If (A$ = "Conversion Table") Then GoTo 10
If (Sheets(A$).Range("$C$1").Value = "") Then GoTo 10
' Process the current sheet
Range("A" + Format(J)).FormulaR1C1 = "='" + A$ + "'!R1C3"
Range("B" + Format(J)).FormulaR1C1 = "='" + A$ + "'!R2C3"
Range("C" + Format(J)).FormulaR1C1 = "='" + A$ + "'!R37C8"
Range("D" + Format(J)).FormulaR1C1 = "='" + A$ + "'!R38C8"
J = J + 1
10 Next I