How have the worksheets been named?

Unique names or just Sheet1, Sheet2 etc?

If Sheet1 etc. a formula

=INDIRECT("Sheet" & (ROW()-1) & "!E3")

Gives Sheet1!E3. When copied down, A2 will be Sheet2!E3, etc.

If unique, you will have to get the names into a list on one sheet and reference
them from that list.

To list the sheets into a sheet in Column A

Best to insert a new worksheet then run the macro.

Private Sub ListSheets()
'list of sheet names starting at A1
Dim Rng As Range
Dim i As Integer
Set Rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
Rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
Next Sheet
End Sub


Gord Dibben Excel MVP



On Thu, 26 Jan 2006 08:16:04 -0800, "trtfn"
wrote:

I have a workbook with 26 worksheets in it, and on one page I wish for cells
to reference the names on the worksheet tabs.

Gord Dibben MS Excel MVP