Linking Cells in multiple Sheets

[pza]

Hello

I posted this message to beginners but I wondered whether it would be
better here:


I have a workbook with 95 sheets containing information for different
customers and I would like to extract information from 3 cells in each
sheet to a 96th sheet to use this for a mailmerge document.

Basically I wanted the information in B1 of all the sheets to copy to
A2-A96 of the new sheet, i.e.

Sheet 1 B1 Baker
Sheet 2 B1 Toms
Sheet 3 B1 Peters

and what I would like on sheet 96 is

A2 Baker
A3 Toms
A4 Peters
etc

I have achieved this now by using this formula

=INDIRECT("Sheet"&$K1&"!$b$1")

I left the sheets as sheet1, sheet2, sheet3 etc

but now I would like to rename the sheets with the customers name i.e.
Baker, Toms, Peters and so on, I thought that my link would
automatically update but it doesn't.

Can this be done?

Thanks again

 

[Gord Dibben]

The reference won't change if you change the sheet name.

You have hard-coded the work "Sheet" in your formula.

I notice you said 3 cells from each sheet then you went on to just B1 from each
sheet.

Which is it?

I would go with a small macro to list the names from B1 on a new blank sheet.

Sub names()
'list of B1 from each sheet into A2:A96
Dim ws As Worksheet
X = 2
Worksheets.Add.Name = "Newsheet"
For Each ws In ActiveWorkbook.Worksheets
Cells(X, 1).Value = ws.Range("B1").Value
X = X + 1
Next ws
End Sub


Gord Dibben MS Excel MVP

 

[Roger Govier]

Hi

You could do this with a simple macro.
Name your Sheet96 as Summary, then run the following macro

Sub FillNames()
Dim wsd As Worksheet, i As Long, lr As Long
Set wsd = ThisWorkbook.Sheets("Summary")
wsd.Activate
lr = wsd.Cells(Rows.Count, "A").End(xlUp).Row
wsd.Range(Cells(2, 1), Cells(lr, 1)).ClearContents
For i = 1 To ThisWorkbook.Worksheets.Count
If Sheets(i).Name <> "Summary" Then
wsd.Cells(i + 1, 1) = Sheets(i).Cells(1, 2)
End If
Next
End Sub

To copy the macro to your workbook, Copy the code as above
Press ALt+F11 to invoke the VB Editor
Insert>Module
In the large white pane than appears>Paste
Alt+F11 to return to Excel

To run the Macro, Tools>Macro>macros>highlight FillNames>Run

 

[pza]

Thank you very much, I do have three cells but I thought that I could
adapt the macro to show the other two cells which at the moment I
think will be D2 and G31 but I am not sure.

The macro works beautifully, thank you very much. I am a really Excel
novice and know nothing about macros.

 

[pza]

Thank you, this will make life much easier, yesterday on our first
workbook we did it manually and it took ages.