If you go with Pete's suggestion, here is macro to get the list of sheet names
into column X

Sub CreateListOfSheetsOnFirstSheet()
Dim ws As Worksheet
For i = 1 To Worksheets.Count
With Worksheets(1)
Set ws = Worksheets(i)
.Cells(i, 24).Value = ws.Name
End With
Next i
End Sub


Gord Dibben MS Excel MVP

On Sat, 17 May 2008 05:20:50 -0700 (PDT), Pete_UK wrote:

Thinking further on this, you could avoid having to rename all your
sheets if you were to list them somewhere in your summary sheet (for
example in cells X1:X200). Then you could use this formula in your
first cell:

=INDIRECT("'"&INDEX($X$1:$X
$200,ROW(A1))&"'!"&CHAR(COLUMN(A1)*2+66)&"48")

Note the apostrophes - between the first set of quotes and immediately
before the exclamation mark. These will allow the use of spaces in
your names. Your sheet names in X1:X200 must be typed exactly as they
appear on the tabs, including any leading or trailing spaces.

Now you can copy this across and down as required.

Hope this helps.

Pete

On May 17, 2:50*am, Pete_UK wrote:
Ah! *I thought from your first posting that the sheet names would have
a number in them that could be incremented, like Sheet1, Sheet2 etc. I
can't see how you can do that now if you first sheet name is:

A. *Vision Mission Values

Basically, though, to do what you want you would have to use the
INDIRECT function - this allows you to build up sheet and cell
references as if they were strings. You can make use of the ROW and
COLUMN functions to increment the cell references, something like:

=INDIRECT("'Sheet"&ROW(A1)&"'!D48")

This will give you the first thing you asked for, and you could just
copy this into the 9 other columns on that row and manually change D48
to F48, H48, J48 etc, before copying the formula down your 200 rows.

If you wanted the D to change automatically to the next letter but two
each time, then you could amend the formula to this:

=INDIRECT("'Sheet"&ROW(A1)&"'!"&CHAR(COLUMN(A1)*2+ 66)&"48")

Then you could copy this across 9 columns and then copy these 10
columns down.

However, you would need to change your sheet names for this to work.

Pete