Employee Training Tracking and Reporting Tool

ddutton

New Member
Joined
Jan 9, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows
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.
 

Attachments

  • Example Training Tracking Tool.jpg
    Example Training Tracking Tool.jpg
    102.5 KB · Views: 23
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Try the following and let me know if i works for you :

VBA Code:
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
 
Upvote 0
Solution
Thank you LOGIT. with a little tweaking, this will definitely help.
 
Upvote 0
You are welcome. Glad to help.
 
Upvote 0

Forum statistics

Threads
1,226,882
Messages
6,193,481
Members
453,803
Latest member
hbvba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top