Take a value from several worksheets and put it in a column
 

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?

Take a value from several worksheets and put it in a column
 

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?

Take a value from several worksheets and put it in a column
 

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?

Take a value from several worksheets and put it in a column
 

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?