SUMMARY-
require a sheet in an excel workbook which lists data contained in
multiple worksheets.  Data is contained in matching cells on several
sheets. Number of sheets will vary and thus length of list will vary
to match.  No consolidation needed, only to return value (text and
numerical) of cells.

DESCRIPTION-
I utilize an excel file to track hours at several branches of myh
company.  I would like a summary page added to this workbook which
automatically creates a list from data contained on all the worksheets
in the workbook.

Based on which comapny branch is filling in the workbook, the number
of sheets could vary from 5 to 50.

A sample of the workbook can be found here http://www.savefile.com/files/1404630  

I have mocked up the first page 'SUMMARY' to show the data required
and its location on the subsequent pages.

Thank you for your help.

Hello Chriswebb,

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.

[0] Open the worksheet (or select it if its already open).

[1] Use the menu
  Tools -> Macro -> Visual Basic Editor

[2] 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.

[3] 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
macro below).

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
the worksheet.

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:
  http://office.microsoft.com/en-us/assistance/HA011403071033.aspx
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.

  --Maniac

Macro to generate the summary page

Sub MakeSummary()
'
' MakeSummary Macro
' Macro created 6/12/2006 by Maniac
'

'
    Sheets("SUMMARY").Select
'   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
End Sub

Script performed very well, I appreciate the additional explaination
of the elements in the code which allowed me to make some additional
adjustments.  Instead of running the script via macro command bar, I
ended up applying the script to an embedded button on the sheet which
would be more understandable to the end user and performed refreshes
more easily.