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?

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?