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)