Linking cells from multiple sheets into one "summary" sheet

[jamesfry1]

Hi,

I have a work book that has about 20 pages in it. Each page has the same kind of data on about different products

I want to sum them up in a final worksheet that groups them all together in one table.

If on the final worksheet I link to the first sheet cell A9, i.e. Sheet1!A9 is there a way I can copy the formula down the column so that the next row is Sheet2!A9 and the next one is Sheet3!A9 without manually inputting these formulas?

Normally if I drag the formula down it will stay on worksheet1! and will move to A10, then A11 and so on- it will change the cell reference not the worksheet reference.

Any help you can give me would be massively appreciated.

Kind regards

James

<colgroup><col></colgroup><tbody>
</tbody>

<tbody></tbody>

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[Boltie]

You could use the INDIRECT function.

Summary Sheet A9 =INDIRECT("Sheet"&ROW(A9)-8&"!A9")

You can then expand this down and have a list of all the A9's for all your sheets. This would only work if your sheets were named the same bar the number. I'm going to hazard a guess that yours have names.

If you are going to report on more than one column/row from each sheet though, typing the sheet names in a list on the summary sheet and using the indirect function with that will still save you time in copying and pasting or typing manually I'm sure.

 

[jamesfry1]

You could use the INDIRECT function.

Summary Sheet A9 =INDIRECT("Sheet"&ROW(A9)-8&"!A9")

You can then expand this down and have a list of all the A9's for all your sheets. This would only work if your sheets were named the same bar the number. I'm going to hazard a guess that yours have names.

If you are going to report on more than one column/row from each sheet though, typing the sheet names in a list on the summary sheet and using the indirect function with that will still save you time in copying and pasting or typing manually I'm sure.

Thanks a lot for that Boltie, it worked an absolute treat.

 

[Oeldere]

There are also VBA solutions available for pulling all data in a summary sheet. Are you able to work with VBA?

 

[jamesfry1]

I have some experience with VBA, what solutions does it offer?

Any feedback again appreciated

James

 

[Oeldere]

I use this code for consolidating sheets on the sheet consolidated

Sub integratie_Oeldere_revisted_vs2()
Dim wsTest As Worksheet
'check if sheet "Consolidated" already exist
Const strSheetName As String = "Consolidated"
 
Set wsTest = Nothing
On Error Resume Next
Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
On Error GoTo 0
 
If wsTest Is Nothing Then
    Worksheets.Add.Name = strSheetName
End If
With Sheets("Consolidated")
    .UsedRange.ClearContents
    .Range("A1:E1").Value = Array("sheet", "Project", "in budget", "cost incurred", "balance available")
    For Each sh In Sheets
        With sh
            If .Name <> "Consolidated" Then
                rng = .Cells.Find("*", , , , xlByRows, xlPrevious).Row - 1
                NR = Sheets("Consolidated").Cells.Find("*", , , , xlByRows, xlPrevious).Row + 1
                If rng > 0 Then
                    Sheets("Consolidated").Cells(NR, 1).Resize(rng) = .Name
                    Sheets("Consolidated").Cells(NR, 2).Resize(rng, 4) = .Range("A4").Resize(rng, 4).Value
                End If
            End If
        End With
    Next
    .Range("C2:C" & .Rows.Count).SpecialCells(4).EntireRow.Delete
    .Columns("A:Z").EntireColumn.AutoFit
End With
End Sub

 

[jamesfry1]

Cheers, I'll be checking that out this weekend.