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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question