Growing Workbook w/ Summary Page

[CoalTrainLady]

Without boring you with the actual details of my job, what I am trying to do is take multiple worksheets and either combine them into one worksheet with a summary page, or if there is another way to get a summary page that is fine too. The summary page needs to "grow" as more worksheets are added. I would like as much of this process to be automated as possible. Does this need VBA, macro, vlookup? Most of the data is [mm/dd/yy hh:mm] type fields, but some are text. I'm lost as to how to make the summary page grow and also would really rather not have to re-set up the formulas every time I add a new tab/worksheet as there will be several hundred in a year's time.

I have done this before at my last job so I know it can be done, just cannot remember for the life of me how to do it. Grr.

Let me know if more detail is needed. Thank you!

[zbor]

You could NAME first AND last SHEET and then add every new sheet BETWEEN first and last in the workbook.

That way you could use:

=SUM(FIRST:LAST!1:1))

That would give you SUM from first row in every sheet

[CoalTrainLady]

I like that idea... I guess I left out some details though, the summary page has multiple columns though. Example:

Shipment ID, Trainset Name, Load Date, Unload Date, Unload Time (Gross), Unload Time (Net), Queue Time

where each one is a column, then the rows need to grow to fill in the data per the worksheets. So:

BM502 R03 1/2/2011 1/4/2011 12:10 7:24 0:00
BM001 R01 1/3/2011 1/5-1/6 8:30 7:30 1:00
BM002 B01 1/6/2011 1/8/2011 16:45 7:24 0:00
BM504 R03 1/6/2011 1/9-1/10 18:00 7:27 0:00

where this data is available in worksheets, each shipment ID has its own sheet. If that makes any sense.

[zbor]

You can reference to one cell in each sheet...

=SUM(FIRST:LAST!D1)

However, you need to adopt sheet's to fulfil this issue.

[watersev]

hi, CoalTrainLady, it would be helpful to see a sample workbook with original data and the result you need to obtain

[CoalTrainLady]

I don't need it to sum or anything, just report what is on each individual worksheet in one summary page, but I don't want to have to relink the formula each time I add a worksheet, as by the end of the year there will be 500ish worksheets. Yes, the same data is in the same cell on each sheet. SAMPLE SUMMARY PAGE:

Shipment ID Trainset Load Date Unload Date Unload GROSS Unload NET Queuing Time
BM502 R03 1/2/2011 1/5/2011 14:15 7:30 1:20
BM001 R01 1/3/2011 1/8/2011 15:20 6:45 0:40
BM002 B01 1/6/2011 1/9/2011 9:10 7:45 2:10


where this data is in sheets by Shipment ID. I hope this makes sense.