Lookup over multiple sheets

[ferijen]

Hi,

I've made a multi-paged workbook from the 'show pages' option from a pivot table. I've since manipulated the data on each sheet.

On each sheet B1 is a name (from the original pivot table) and B2 is a number (which has been created by me from the data).

I would like to create a list on another page which draws that data from each sheet. I could create the names in a list as those are a standard so I could do a lookup across the sheets, but that doesn't seem to work. Alternatively, any automatic formula which meant that the first cell in the first row on the new sheet read =Nameofsheet1!B1, the next cell read =Nameofsheet1!B2, and the next row read =Nameofsheet2!B1 (etc. etc.) would work.

Does anyone have any other ways of approaching this problem, short of manually doing the formulas (as it's well over 100 sheets, that's something I'd prefer to avoid).

 

[NBVC]

If, on the new sheet, you start in row 1 and as long as the end of each sheet name contains a number starting from 1 and running consecutively to the last sheet#, you can try these formulas in adjacent cells and then copied down.

=INDIRECT("Nameofsheet"&ROW()&"!"&"B1")
&
=INDIRECT("Nameofsheet"&ROW()&"!"&"B2")

 

[ferijen]

No, every sheet had a different word-name.

I did, however, finally manage to get a macro to do it for me (courtesy of a colleague)

The macro, in case anyone searches on this:

Sub Summarise()
Dim sheetCount As Integer
Dim numSheets As Integer

Worksheets(1).Activate

Response = MsgBox("...reset this sheet?", vbYesNo, "Do you want to...")

If Response = vbYes Then
ActiveSheet.Range("A1", ActiveSheet.Range("A1").End(xlDown)).EntireRow.Delete

Range("A1").Select

numSheets = Sheets.Count

For sheetCount = 2 To numSheets
ActiveCell.Value = Worksheets(sheetCount).Range("B1")
ActiveCell.Offset(0, 1).Value = Worksheets(sheetCount).Range("B2")
ActiveCell.Offset(0, 2).Value = Worksheets(sheetCount).Range("C1")
ActiveCell.Offset(1, 0).Select
Next
End If
End Sub