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" <trtfn@discussions.microsoft.com>
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
Bookmarks