I have a work book with 74 worksheets. These sheets are arranged exactly the
same way so that all data has a particular address. I want to take cell B3
from all sheets and populate a column of the master worksheet. I want to do
this with several of the cells. What is the fastest way to do this?

List the sheet names that you want to take data from in column A of
your summary sheet, then use this formula in column B:

=INDIRECT("'"&A1&"'!B3")

Copy this down as required.

Hope this helps.

Pete

On Oct 7, 6:56*pm, pstepp wrote:
I have a work book with 74 worksheets. These sheets are arranged exactly the
same way so that all data has a particular address. I want to take cell B3
from all sheets and populate a column of the master worksheet. I want to do
this with several of the cells. What is the fastest way to do this?

If the sheets are named Sheet1, Sheet2 etc. enter in A1 of summary sheet.

=INDIRECT("Sheet" & (ROW()) & "!B3")

If unique names, go with Pete's method and formula.

To easily get a list of all sheets except first(summary) into column A of
summary sheet run this macro.

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


Gord Dibben MS Excel MVP


On Tue, 7 Oct 2008 10:56:04 -0700, pstepp
wrote:

I have a work book with 74 worksheets. These sheets are arranged exactly the
same way so that all data has a particular address. I want to take cell B3
from all sheets and populate a column of the master worksheet. I want to do
this with several of the cells. What is the fastest way to do this?

Thanks that is what I was looking for.

"Gord Dibben" wrote:

If the sheets are named Sheet1, Sheet2 etc. enter in A1 of summary sheet.

=INDIRECT("Sheet" & (ROW()) & "!B3")

If unique names, go with Pete's method and formula.

To easily get a list of all sheets except first(summary) into column A of
summary sheet run this macro.

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


Gord Dibben MS Excel MVP


On Tue, 7 Oct 2008 10:56:04 -0700, pstepp
wrote:

I have a work book with 74 worksheets. These sheets are arranged exactly the
same way so that all data has a particular address. I want to take cell B3
from all sheets and populate a column of the master worksheet. I want to do
this with several of the cells. What is the fastest way to do this?