Sum data across sheets - if certain value is selected

[svjensen]

I have a number of identical sheets containing similar data (data in cells E10 through F15. These are all placed within two "limiters" - 'start ->' and '<- end'.

On my summation sheet I sum up the data be using '=SUM(start ->:<- end!E10)' in cell E10 (and so on until F15).

Now I would like to sum up only the data from sheets which have a value in cell A1, which is identical to the one in A1 on my summation sheet.

If you need more explanatio please let me know.

/Soren

 

[ravishankar]

Hi
Insert / rename a sheet as Total. Paste the following codes in the macro window (alt f11) and run the macro.

Sub Soren()
Dim a As Long, b As Long, c As Long
Worksheets("Total").Range("E10:F15").ClearContents
    For a = 1 To Sheets.Count
        If Worksheets(a).Name <> "Total" Then
            For b = 5 To 6
                For c = 10 To 15
                    If Worksheets(a).Cells(1, 1) <> "" And Worksheets(a).Cells(1, 1) = Worksheets("Total").Cells(1, 1) Then
                    Worksheets("Total").Cells(c, b) = Worksheets("Total").Cells(c, b) + Worksheets(a).Cells(c, b)
                    End If
                Next c
            Next b
        End If
    Next a
MsgBox "Summation is complete"
End Sub

Ravi

 

[sanrv1f]

try this

E10

=SUMPRODUCT(
          SUMIF(INDIRECT(G1:G10 & "!A1"),A1,
                  INDIRECT(G1:G10 & "!" & ADDRESS(ROW(),COLUMN()))))

where G1:G10 houses the list of sheet names, excluding the active sheet

 

[MyExcel]

u can use this code to get all sheets name in range G
to use Sankar Formula easy

Sub SheetNames()
    For i = 1 To Sheets.Count
    Sheets("Sheet1").Range("g" & i).Value = Sheets(i).Name
    Next i
End Sub