Linking Cells in multiple Sheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pza@btinternet.com - 21 Jan 2008 22:49 GMT

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

Reply to this Message

Gord Dibben - 21 Jan 2008 23:38 GMT

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

>Hello
>
[quoted text clipped - 32 lines]
>
>Thanks again

Reply to this Message

pza@btinternet.com - 22 Jan 2008 07:32 GMT

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.

>The reference won't change if you change the sheet name.
>
[quoted text clipped - 56 lines]
>>
>>Thanks again

Reply to this Message

Roger Govier - 21 Jan 2008 23:39 GMT

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

Signature

Regards
Roger Govier

> Hello
>
[quoted text clipped - 32 lines]
>
> Thanks again

Reply to this Message

pza@btinternet.com - 22 Jan 2008 07:33 GMT

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

>Hi
>
[quoted text clipped - 21 lines]
>
>To run the Macro, Tools>Macro>macros>highlight FillNames>Run

Reply to this Message