I have a workbook with 30+ sheets.
Each sheet is showing a timesheet comparison over several months before and
after a payrise. Each sheet is laid out identically - it's just the values
that change.
I'm building a summary page that I would like to to show the before and
after values without having to look at the complete sheet.
It's likely that the values on the comparison sheets will change after the
summary is complete so having it auto update when an individual sheet is
changed would make working on this book much easier. It's also possible that
new sheets will be added so being able to add them in quickly would be useful.

Building it cell by cell is simple - I'm just having the relevant cell in
the summary sheet equal the appropriate cell from the comparison sheet so the
formula looks like:
='sheet1'!L40 (as one example)
Is there any way to be able to copy this data down the summary sheet and
have the formula update accordingly?
ie to copy as ='sheet2'!L40 rather than ='sheet1'!L52 if I was copying to a
cell 12 spaces down. (please not

I'm guessing that if I could copy the cell reference as ='sheet1'!$L$40 I'd
be part way there. I would prefer not to have to manually alter each
reference after cutting and pasting it, or to manually enter the cell
reference data hundreds of times.

I'd also like ideally to be able to use the actual tab name of each sheet as
a reference on the summary sheet: ='sheet1'
I presume I'm missing the right characters to allow Excel to do this.

This is on Excel 2000 (and would need to be viewed in all later versions)

Thanks in advance for any help

Tom