I cannot figure out if this is possible or not.

I have a workbook with 51 worksheets in it. The first sheet is a Total
sheet and is supposed to show a summary of each of the other 50 worksheets.
So I need to figure out how to get cell A5 from Sheet2 to show up on the
Total sheet, then in the next row I need cell A5 from Sheet3, below that cell
A5 from Sheet4, etc for all 50 worksheets.
I set the first one to "=Sheet1!$A$5" and then tried to copy it hoping it
would copy to read "=Sheet2!$A$5" but it copies as "=Sheet1!$A$5".
Any ideas on how to set it up so that I can copy the formula and have it
change the sheet name without manually typing them all in or clicking on a
box in each sheet?

Put this somewhere in the Total sheet and copy down:

=INDIRECT("Sheet" & ROWS($A$1:A1) & "!A5")


--
Gary''s Student - gsnu200766


"nick" wrote:

I cannot figure out if this is possible or not.

I have a workbook with 51 worksheets in it. The first sheet is a Total
sheet and is supposed to show a summary of each of the other 50 worksheets.
So I need to figure out how to get cell A5 from Sheet2 to show up on the
Total sheet, then in the next row I need cell A5 from Sheet3, below that cell
A5 from Sheet4, etc for all 50 worksheets.
I set the first one to "=Sheet1!$A$5" and then tried to copy it hoping it
would copy to read "=Sheet2!$A$5" but it copies as "=Sheet1!$A$5".
Any ideas on how to set it up so that I can copy the formula and have it
change the sheet name without manually typing them all in or clicking on a
box in each sheet?

If your sheets are named sheet2, sheet3, etc, this will put the formula in
col a of the active sheet

Sub getsheetformulas()
For i = 2 To Sheets.Count
Cells(i, 1).Formula = "=sheet" & i & "!a5"
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"nick" wrote in message
...
I cannot figure out if this is possible or not.

I have a workbook with 51 worksheets in it. The first sheet is a Total
sheet and is supposed to show a summary of each of the other 50
worksheets.
So I need to figure out how to get cell A5 from Sheet2 to show up on the
Total sheet, then in the next row I need cell A5 from Sheet3, below that
cell
A5 from Sheet4, etc for all 50 worksheets.
I set the first one to "=Sheet1!$A$5" and then tried to copy it hoping it
would copy to read "=Sheet2!$A$5" but it copies as "=Sheet1!$A$5".
Any ideas on how to set it up so that I can copy the formula and have it
change the sheet name without manually typing them all in or clicking on a
box in each sheet?

That worked! Thanks!

"Gary''s Student" wrote:

Put this somewhere in the Total sheet and copy down:

=INDIRECT("Sheet" & ROWS($A$1:A1) & "!A5")


--
Gary''s Student - gsnu200766


"nick" wrote:

I cannot figure out if this is possible or not.

I have a workbook with 51 worksheets in it. The first sheet is a Total
sheet and is supposed to show a summary of each of the other 50 worksheets.
So I need to figure out how to get cell A5 from Sheet2 to show up on the
Total sheet, then in the next row I need cell A5 from Sheet3, below that cell
A5 from Sheet4, etc for all 50 worksheets.
I set the first one to "=Sheet1!$A$5" and then tried to copy it hoping it
would copy to read "=Sheet2!$A$5" but it copies as "=Sheet1!$A$5".
Any ideas on how to set it up so that I can copy the formula and have it
change the sheet name without manually typing them all in or clicking on a
box in each sheet?