Multiple Worksheet reference in a formula

[Mike Maguire]

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?

 

[Gord Dibben]

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