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

[pstepp]

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?

 

[Pete_UK]

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

 

[Gord Dibben]

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

 

[pstepp]

Thanks that is what I was looking for.

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