Summary and Lookup from multiple sheets within the files

[Did13n]

Hi Everyone

i am not familiar with macros actually as all done usually by using the formulas
but i might need help here to see whether it can be done by macros as i dont have references to do it with formulas

i would like to have summary reports in Sheet1 with references from Other sheets based on the Criteria

details you might found in the attached files

by the way, i am using Excel 2010 version

thanks a lot

[mohadin]

Hi
Try

Sub test()
    Dim a As Variant
    Dim d, i
    Dim sh As Worksheet
    Application.ScreenUpdating = False
    d = Sheets("Sheet1").Cells(1, 2)
    With CreateObject("scripting.dictionary")
        For Each sh In Worksheets
            If sh.Name <> "Sheet1" Then
                a = sh.Cells(2, 1).CurrentRegion.Value2
                For i = 1 To UBound(a)
                    If a(i, 2) = d Then .Item(a(i, 1) & "#" & a(i, 3)) = .Item(a(i, 1) & "#" & a(i, 3))
                Next
            End If
        Next
        Application.DisplayAlerts = False
        Sheets("sheet1").Cells(4, 1).Resize(.Count) = Application.Transpose(.keys)
        Sheets("sheet1").Cells(4, 1).Resize(.Count).TextToColumns Destination:=Range("A4"), OtherChar:="#", FieldInfo:=Array(Array(2, 1))
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
    End With
End Sub