Copying as absolute values across multiple sheets

[Guest]

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

 

[Guest]

In row 1:
=INDIRECT("Sheet" & ROW() & "!L40") and fill down.
This will give you Sheet1!L40 then Sheet2!L40, etc.

 

[Guest]

Hi Bob,

thanks for the prompt response. However, I can't enter your formula without
getting a #REF! warning.
I've tried entering it exactly as you have and with different sheet names,
cell values and can't get the value to be shown. The cell I'm referencing has
a sum formula in it, which may be the issue.

I also can't use the fill tool once I get it working as each cell is to be
copied down to another cell 12 rows down (so if the reference to 'Sheet1'L40
was in cell C2, 'Sheet2'L40 would need to be in cell C14, 'Sheet3'L40 in C26
and so on)

I may just have to bite the bullet and copy/paste absolute cell references
then change the sheet name in each entry

But any further help on this would be appreciated

Tom

 

[Peo Sjoblom]

Bob's formula assumes that your sheet are named Sheet1, Sheet2 and so on
just the way you posted.
For future posts don't try to simplify sheet names etc if you ask about
formulas that deal with sheet names.

If there aren't any method in where you can use the sheet names as such you
can't use this
at all, it assumes that there is a text string that is the same for all
sheets then adds an index number to it. The best you could do would be to
use a list with all sheet names if they are that different from each other
and refer to that list, assume the sheet names are in K1:K30 in the same
sheet that holds the formula

=INDIRECT("'"&K1&"'!L40")

copied down will return what's in L40 in the sheet names that are in K1, K2,
K3 and so on


--


Regards,


Peo Sjoblom

 

[Guest]

Hi there,

Bob's formula assumes that your sheet are named Sheet1, Sheet2 and so on
just the way you posted.
For future posts don't try to simplify sheet names etc if you ask about
formulas that deal with sheet names.

Sorry about that. I was assuming (which was silly, of course) that I would
be able to substitute sheet names into any formula given. Given that I am
working on timesheets, I do prefer not to reveal any more information than I
have to but I do get what you are saying and will remember it for future.

If there aren't any method in where you can use the sheet names as such you
can't use this
at all, it assumes that there is a text string that is the same for all
sheets then adds an index number to it. The best you could do would be to
use a list with all sheet names if they are that different from each other
and refer to that list, assume the sheet names are in K1:K30 in the same
sheet that holds the formula

=INDIRECT("'"&K1&"'!L40")

copied down will return what's in L40 in the sheet names that are in K1, K2,
K3 and so on

Thanks for this. Between you and Bob, and searching the discussion group in
more depth I should have enough to be able to do this.

Cheers.

Tom