Employee Training Tracking and Reporting Tool

[ddutton]

I am creating a training tracking tool for my small company and need some excel guidance from the "guru's"... Thank you all in advance for any advice you can give me.
I have a list of trainings with the associated information I am manually entering regularly. Many trainings are held yearly and are classified differently by their frequency. With up to 40 different employees that need to be tracked as attending each training, I need to be able to report on a separate tab in the worksheet that will show each of the trainings attended from all the trainings held. I need to populate the report with a list and provide specifics for their supervisors to see when doing reviews and 1:1 meetings with the employees. The report needs to show each class that a specified student selected in the report attended. Hopefully this is enough data to provide some result.

 

[Micron]

Thank you all in advance for any advice you can give me.

Except for maybe this answer: the right tool for projects like this is a relational database. I suspect you will not want to go there unless you have lots of time to learn something like MS Access. I'd also bet that there would be db templates available which would cut down the time it would take, except it seems that no template is ever good enough right off the shelf. Access has a steep learning curve if you want to avoid the many mistakes that novices make. Or you could hire a developer. Last but not least I suppose someone may come along here after having blazed this trail already. Whatever you end up with in Excel it will never be as robust as a real db. At least that's my take on it.

 

[ddutton]

unfortunately (and fortunately) you may be right. I just hoped there was a way to pull the information from one sheet and compile it on the next.. thanks.

 

[Micron]

I just hoped there was a way to pull the information from one sheet and compile it on the next

No doubt there is. But to me it would be like installing screws with a hammer. Probably can be done in some scenarios, but certainly not the best approach.

 

[Logit]

Try the following and let me know if i works for you :

Option Explicit

Sub CopyAndSortTrainingData()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim lastRowSource As Long
    Dim lastRowTarget As Long
    Dim i As Long
    Dim j As Long
    Dim traineeName As String
    
    ' Set the worksheets
    Set wsSource = ThisWorkbook.Sheets("Training Attendance Sheet")
    Set wsTarget = ThisWorkbook.Sheets("Training Tracker Report")
    
    ' Find the last row in the source sheet
    lastRowSource = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    
    ' Initialize the target row
    lastRowTarget = 6
    
    ' Loop through each row in the source sheet
    For i = 2 To lastRowSource
        ' Loop through each trainee column (F to O)
        For j = 6 To 15
            If wsSource.Cells(i, j).Value = "X" Then
                ' Get the trainee name from the header row
                traineeName = wsSource.Cells(1, j).Value
                
                ' Copy the data to the target sheet
                wsTarget.Cells(lastRowTarget, 2).Value = wsSource.Cells(i, 4).Value ' Date
                wsTarget.Cells(lastRowTarget, 3).Value = wsSource.Cells(i, 1).Value ' Title
                wsTarget.Cells(lastRowTarget, 4).Value = wsSource.Cells(i, 3).Value ' Frequency
                wsTarget.Cells(lastRowTarget, 5).Value = traineeName ' Trainee
                wsTarget.Cells(lastRowTarget, 6).Value = wsSource.Cells(i, 5).Value ' Trainer
                
                ' Move to the next row in the target sheet
                lastRowTarget = lastRowTarget + 1
            End If
        Next j
    Next i
    
    ' Add a helper column for numeric sorting
    With wsTarget
        ' Find the last row in the target sheet
        lastRowTarget = .Cells(.Rows.Count, "B").End(xlUp).Row
        
        ' Populate the helper column
        For i = 6 To lastRowTarget
            .Cells(i, 7).Value = CLng(Mid(.Cells(i, 5).Value, InStrRev(.Cells(i, 5).Value, " ") + 1))
        Next i
        
        ' Sort the data by the helper column
        .Range("B6:G" & lastRowTarget).Sort Key1:=.Range("G6"), Order1:=xlAscending, Header:=xlNo
        
        ' Remove the helper column
        .Range("G6:G" & lastRowTarget).ClearContents
    End With
End Sub

 

[ddutton]

Thank you LOGIT. with a little tweaking, this will definitely help.

 

[Logit]

You are welcome. Glad to help.