Lookup over multiple sheets

ferijen

New Member
Joined
Nov 16, 2004
Messages
12
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).
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

New Member
Joined
Nov 16, 2004
Messages
12
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,111
Messages
5,570,254
Members
412,313
Latest member
pauloalex
Top