Relative worksheet reference for multiple worksheets in a workbook

[Debbie]

I have a workbook with several worksheets. I am creating a summary worksheet
page within the workbook. Here is what I have:
=+'Sheet1 (3)'!$H$52
I need to be able to copy that for several rows on the summary worksheet
page AND have it automatically change the sheet reference to the next sheet.
Example:
=+'Sheet1 (3)'!$H$52
=+'Sheet1 (4)'!$H$52
=+'Sheet1 (5)'!$H$52
When I copy and paste it keeps the sheet reference the exact same and
doesn't change the sheet reference to the next # automatically. Please HELP
if you know how. I have done about 2 hours of research and can't figure it
out

 

[Dave Peterson]

I'd try:

=indirect("'Sheet1 (" & row() - ## & ")'!h52")

The ## depends on the row that's getting the formula.

So if you the first formula goes into row 2 and I want to retrieve the value
from "sheet1 (1)", then the ## would be 1 (so it's the row (2) - 1 = 1).

 

[Debbie]

Dave,
OK I think I may have presented it incorrectly....I need it to reference the
next SHEET not the next row. See below for my corrected problem. Hopefully
this will make sense Any help would be greatly appreciated.
I am creating a summary worksheet page within a multiple worksheet workbook.
Here is what I have:
=+'Sheet1'!$H$52
I need to be able to copy that for several rows on the summary worksheet
page AND have it automatically change the sheet reference to the next sheet.
Example of what I need when I copy and paste:
=+'Sheet1'!$H$52
=+'Sheet2'!$H$52
=+'Sheet3'!$H$52
So the sheet reference is relative but the cell reference is absolute.

 

[JLatham]

I posted much the same answer that Dave has here in your other posting of
same question in GQ. Best to only ask in one forum so that all answers can
be consolidated. Or maybe the system split this response into two different
forums - who knows.

I believe that the response that Dave has given you is the correct one for
what you want. But I think Dave may have messed up his formula by adding
parenthesis in it that aren't needed. Try
=INDIRECT("'Sheet" & Row()-## & "'!H52")
same explanation of ROW()-## as he gave: it should be a number that when
subtracted from the current row gives you a 1 for first formula entry. Then
it will increment as you copy the formula down the sheet. So if first
formula went:

Into row 1 and you want reference to Sheet1 it is simply:
=INDIRECT("'Sheet" & Row() & "'!H52")

Into row 2 and you want reference to Sheet1 for first formula, it would be:
=INDIRECT("'Sheet" & Row()-1 & "'!H52")

Into row 5 and you want a reference to Sheet1 for first formula, then it
would be:
=INDIRECT("'Sheet" & Row()-4 & "'!H52")

 

[JLatham]

My apologies to Dave - actually his formula here is correct for the sample
data you provided, which is different than what I saw in your other post.
The parenthesis would be needed to form the proper name of the duplicated
sheets. Just a little more confusion from cross-posting in multiple forums.

 

[Dave Peterson]

I was going to respond to the OP, but I'm not sure what Debbie's worksheet names
really are.