You could use Pete's INDIRECT formula if you had a list of the sheetnames in a
worksheet.

Run this macro to get the list of 68 sheetnames on a new sheet named List.

Private Sub ListSheets()
Dim rng As Range
Dim i As Integer
Worksheets.Add(after:=Worksheets(Worksheets.Count) ).Name = "List"
Set rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
Next Sheet
End Sub

Then on Table sheet alter Pete's formula to...................

=INDIRECT(List!A1&"!C$11")


Gord Dibben MS Excel MVP

On Thu, 27 Sep 2007 07:17:26 -0700, wrote:

On Sep 27, 9:44 am, Pete_UK wrote:
Are your worksheets numbered from Sheet2 to Sheet68? If you want to
get information from the same cell of the other worksheets, for
example cell K13, then you could use something like this:

=INDIRECT("Sheet"&ROW()&"!K$13")

Put the first formula in row 2 of Sheet1, then copy down.

Hope this helps.

Pete

On Sep 27, 2:09 pm, wrote:



How can I change only the worksheet when I drag down a cell
reference. these are all worksheets in an active workbook. We are
creating a summary table in worksheet one from 68 worksheets in that
workbook and the cells referenced are always the same but in
sequential worksheets. Thank you!- Hide quoted text -

- Show quoted text -

The sheets all have names such as iye, iyz, iyh. The data is on sheet
iyz (or iye, iyh), cell C11 and needs to go to sheet Table, cell A3,
A4, A5...down.

Thank you!