collate results from a different excel file

[mtwa1990]

I have a master excel file that we use daily, I would like to be able to get some information from that file into another.

I have attached 2 files, one is the master file, the other is the results file that I would like to get the information on.


I have done something similar with all the information in one document but im now stuck on how to get it all from another document. I have written text boxes in the results fie to explain what I would like the results to be and included an example for one of them.

I hope this can be done I cant figure it out so thought it would be a great challenge for somebody.

Thank you in advance

[dflak]

I don't see why you want this in a separate spreadsheet. In order to be interactive, all the data and supporting sheets need to be there. We could hide the sheets you don't need to see.

This application uses Excel Tales (because they know how big they are, so when you add new data, all you need to do is refresh the pivot table) and pivot tables and overlaying some pivot table results with named dynamic ranges.

I had a question about the YTD. I assumed it is for the group selected where the item is not completed.

If this is what you are looking for and you need an explanation, give me a shout.

[mtwa1990]

looks good but unfortunately they have to be separate files

[dflak]

OK, I can copy it all to a separate file and hide those sheets you don't need to see. I'll get on it tomorrow.

[Tsjallie]

OK, I can copy it all to a separate file and hide those sheets you don't need to see. I'll get on it tomorrow.

Why not create a copy of the Master sheet in the Report workbook using Get & Transform. That would be updated each time you open the Reports workbook. And no need to have the Master file opened.

[dflak]

Why not create a copy of the Master sheet in the Report workbook using Get & Transform. That would be updated each time you open the Reports workbook. And no need to have the Master file opened.

Wouldn't this require that the Master Workbook be present? What would happen if the report were mailed off to someone else?

[mtwa1990]

that would be fantastic if you can, thank you

[mtwa1990]

that's sounds like a great idea if it will work easier that way

[dflak]

Should you decide to go with the complete copy, here it is.

There is a control panel sheet that calculates what the Report file name should look like. Fill in the name of the report in Cell B3 and the program will produce a report with that name with a date stamp.

[dangelor]

See if this works...

Sub Results()
    Const WEEK_NUMBER As String = "E3"
    Const GROUP_NUMBER As String = "H3"
    Const AREA_START As String = "B9"
    
    Dim wbResults As Workbook, wbMaster As Workbook
    Dim rData As Range
    Dim s As String, d As String, dd As String
    Dim v As Variant, vID As Variant
    Dim x As Long, g As Long, y As Long
    
    On Error Resume Next
    Set wbMaster = Workbooks("Master Tracker.xlsx")
    On Error GoTo 0
    If wbMaster Is Nothing Then
        MsgBox "The Master Tracker workbook is not open." & vbNewLine & vbNewLine & "Open file and try again.", vbExclamation, "Needed Workbook isn't Open"
        Exit Sub
    End If
    Set wbResults = ThisWorkbook
    If wbResults.Worksheets("Managers").Range(GROUP_NUMBER).Value2 = "" Then
        
        'get group numbers
        s = UniqueList("Group", g)
        With wbResults.Worksheets("Managers").Range(GROUP_NUMBER).Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:=s
        End With
        MsgBox "Select a Group Number and try again.", vbExclamation, "Group Number Missing"
        Exit Sub
    End If
    wbResults.Worksheets("Managers").Range("b10:h100").ClearContents
    g = wbResults.Worksheets("Managers").Range(GROUP_NUMBER).Value2
    
    'get current week number
    v = wbResults.Worksheets("Settings").Range("a1").CurrentRegion
    For x = 2 To UBound(v)
        If v(x, 2) <= Date And v(x, 3) >= Date Then
            wbResults.Worksheets("Managers").Range(WEEK_NUMBER) = Mid(v(x, 1), 6)
            Exit For
        End If
    Next x
    
    'get areas
    s = UniqueList("Area", g)
    v = Split(s, ",")
    With wbResults.Worksheets("Managers").Range(AREA_START)
        For x = 0 To UBound(v)
            .Offset(x + 1) = v(x)
        Next x
    End With
    
    'get IDs
    s = UniqueList("ID", g)
    v = Split(s, ",")
    With wbResults.Worksheets("Managers").Range(AREA_START)
        For x = 0 To UBound(v)
            .Offset(x + 1, 1) = v(x)
        Next x
    End With
    vID = v
    
    'get less then 7 days
    Set rData = wbMaster.Worksheets("Sheet1").Range("a1").CurrentRegion
    s = ""
    With rData
        For x = 0 To UBound(v)
            d = ">=" & Date - 7
            s = s & WorksheetFunction.CountIfs( _
            .Columns("B"), vID(x), _
            .Columns("C"), g, _
            .Columns("G"), d, _
            .Columns("O"), "<> Complete") _
            & ","
        Next x
    End With
    s = Left(s, Len(s) - 1)
    v = Split(s, ",")
    With wbResults.Worksheets("Managers").Range(AREA_START)
        For x = 0 To UBound(v)
            .Offset(x + 1, 2) = v(x)
        Next x
    End With
    
    'get 7-14 days
    Set rData = wbMaster.Worksheets("Sheet1").Range("a1").CurrentRegion
    s = ""
    With rData
        For x = 0 To UBound(v)
            '=COUNTIFS(range,">="&date1,range,"<="&date2)
            d = ">=" & (Date - 14)
            dd = "<" & (Date - 7)
            s = s & WorksheetFunction.CountIfs( _
            .Columns("B"), vID(x), _
            .Columns("C"), g, _
            .Columns("G"), d, _
            .Columns("G"), dd, _
            .Columns("O"), "<> Complete") _
            & ","
        Next x
3    End With
    s = Left(s, Len(s) - 1)
    v = Split(s, ",")
    With wbResults.Worksheets("Managers").Range(AREA_START)
        For x = 0 To UBound(v)
            .Offset(x + 1, 4) = v(x)
        Next x
    End With
    
    'get 15 or more days
    Set rData = wbMaster.Worksheets("Sheet1").Range("a1").CurrentRegion
    s = ""
    With rData
        For x = 0 To UBound(v)
            d = ">=" & Date - 365
            dd = "<" & Date - 14
            s = s & WorksheetFunction.CountIfs( _
            .Columns("B"), vID(x), _
            .Columns("C"), g, _
            .Columns("G"), d, _
            .Columns("G"), dd, _
            .Columns("O"), "<> Complete") _
            & ","
        Next x
    End With
    s = Left(s, Len(s) - 1)
    v = Split(s, ",")
    With wbResults.Worksheets("Managers").Range(AREA_START)
        For x = 0 To UBound(v)
            .Offset(x + 1, 6) = v(x)
        Next x
    End With
End Sub
Function UniqueList(sColumn As String, lGroup As Long) As String
    Dim ws As Worksheet, rData As Range, c As Range
    Dim lCol As Long, x As Long, coll As New Collection
    
    Set ws = Workbooks("Master Tracker.xlsx").Worksheets("Sheet1")
    lCol = ws.Rows(1).Find(sColumn).Column
    Set rData = Intersect(ws.Range("a1").CurrentRegion, ws.Columns(lCol)).Offset(1)
    On Error Resume Next
    For Each c In rData
        If sColumn = "Group" Then
            If Not IsEmpty(c) Then coll.Add c.Value, CStr(c.Value)
        Else
            If Not IsEmpty(c) And ws.Cells(c.Row, 3) = lGroup Then coll.Add c.Value, CStr(c.Value)
        End If
    Next
    On Error GoTo 0
    If sColumn = "Group" Then QuickSort coll, 1, coll.Count
    For x = 1 To coll.Count
        UniqueList = UniqueList & coll(x) & ","
    Next x
    UniqueList = Left(UniqueList, Len(UniqueList) - 1)
End Function
Sub QuickSort(coll As Collection, first As Long, last As Long)
    Dim vCentreVal As Variant, vTemp As Variant
    Dim lTempLow As Long
    Dim lTempHi As Long
    
    lTempLow = first
    lTempHi = last
    vCentreVal = coll((first + last) \ 2)
    Do While lTempLow <= lTempHi
        Do While coll(lTempLow) < vCentreVal And lTempLow < last
            lTempLow = lTempLow + 1
        Loop
        Do While vCentreVal < coll(lTempHi) And lTempHi > first
            lTempHi = lTempHi - 1
        Loop
        If lTempLow <= lTempHi Then
            ' Swap values
            vTemp = coll(lTempLow)
            coll.Add coll(lTempHi), After:=lTempLow
            coll.Remove lTempLow
            coll.Add vTemp, Before:=lTempHi
            coll.Remove lTempHi + 1
            ' Move to next positions
            lTempLow = lTempLow + 1
            lTempHi = lTempHi - 1
        End If
    Loop
    If first < lTempHi Then QuickSort coll, first, lTempHi
    If lTempLow < last Then QuickSort coll, lTempLow, last
End Sub

[Tsjallie]

Here's the option with the connection to the Master workbook. No vba needed.
Condition is that the Master workbook must be accessible by the user user the Report workbook (read-only access will do).
If you would be mailing the Report then refresh it before mailing it.
Just change the path to the Master workbook (top-left of the Manager sheet) to yr needs.