Another way to do it would be to create a VBA function as:


Function SheetNames() As Variant
Dim Arr() As String
Dim N As Long
Dim WB As Workbook
ReDim Arr(1 To Application.Caller.Rows.Count)
Set WB = Application.Caller.Worksheet.Parent
With WB.Worksheets
For N = 1 To .Count
Arr(N) = .Item(N).Name
Next N
End With
SheetNames = Application.Transpose(Arr)
End Function


Then, select some vertical range, say F10:F20, type

=SheetNames()

and press CTRL SHIFT ENTER.
Since this is an Array Formula, you *must* press CTRL SHIFT ENTER
rather than just ENTER when you first enter the formula
and whenever you edit it later. If you do this properly,
Excel will display the formula in the Formula Bar enclosed
in curly braces { }. (You do not type the curly braces -
Excel includes them automatically.) The formula will
not work properly if you do not use CTRL SHIFT ENTER. See
http://www.cpearson.com/excel/ArrayFormulas.aspx for lots
more information about array formulas.

Then, you can use a formula like

=INDIRECT("'"&F10&"'!$H$52")

and fill down. The reference to F10 will increment as you fill down,
and thus return Sheet1, Sheet2, and so on. The names of the worksheet
need not have any name pattern or number. Any name will work.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Sat, 29 Aug 2009 16:43:01 -0700, Debbie
wrote:

I believe my question needed a little clarification. I have a workbook with
several worksheet pages. I am creating a summary worksheet page within the
multiple worksheet workbook. Here is what I have:
=+'Sheet1'!$H$52
I need to be able to copy and paste that for several rows on the summary
worksheet page AND have it automatically change the SHEET reference to the
next sheet.
Example:
=+'Sheet2'!$H$52
=+'Sheet3'!$H$52
=+'Sheet4'!$H$52
SO the worksheet page is relative and the cell reference it absolute.
Now, when I copy and paste it keeps the sheet reference the exact same and
doesn't change the sheet reference to the next # automatically.