Linking Cells in multiple Sheets

  • Thread starter Thread starter pza
  • Start date Start date
P

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
 
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
 
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
 
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.
 
Thank you, this will make life much easier, yesterday on our first
workbook we did it manually and it took ages.
 
Back
Top