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).
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,546
Messages
5,838,018
Members
430,526
Latest member
NiceGuyWithExcel2007

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top