If your sheets are the default names of Sheet1 etc. enter this formula in a
summary sheet in e.g. A1
=INDIRECT("Sheet" & (ROW()) & "!A3")
Copy down.
If not default names.................................
This macro will give you a new sheet with the names of sheets in column A
Private Sub ListSheets()
'list of sheet names starting at A1 on new sheet
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
In B1 of "List" sheet enter =INDIRECT(A1 & "!A3")
Double-click on fill handle to copy down.
Gord Dibben MS Excel MVP
On Sat, 23 Aug 2008 08:06:28 -0700, Mike Maguire <Mike
wrote:
I'd like to collect the value from all of the "A3" cells in a series of
worksheets in one workbook. Like: =Sheet2!A3, =Sheet3!A3, =Sheet4!A3, etc,
but instead of typing each sheet's name for every instance, I'd like to list
all the sheets in a column and then have the formulas refer to that column to
pick up the sheet names. Can do?