Creating a Group Training Passport

[Darth_Williams]

Good afternoon all,

Can I pick your collective minds for a second. I am looking to create a training passport whereby I would enter a team leader name and I would get the results from every person in that team.

So A column lists the team members, B column lists the team leaders, C column onwards shows the various training each person has received.

In essence, if I put Dave Williams in the team leader cell, then the following cells underneath will bring back the results for everyone who has Dave Williams as their team leader. My initial thought would be to use an INDEX MATCH (well my first thought was to do this in a pivot table but my line manager didn't like this) but I'm not sure that would work.

Any thoughts, hints and or tips will be most welcome.

Thanks

Dave.

 

[jmacleary]

Hello there. Can't you just use a filter on column B?

 

[Darth_Williams]

On the main registry, yes, which I do all the time, but I am looking to make a stand alone summary sheet that will pull the data from the main matrix to offer a summary page instead of being bombarded with more data than you need to see

 

[jmacleary]

Ah OK. A simple macro would do the job - here is a very basic one that does it - but you will probably want to add error checking, get the name from a cell, etc. Hope it helps.

Sub Summariser()

Dim WhoFor As String
WhoFor = InputBox("Which boss?")
   
    ActiveSheet.Range("A1:z1").AutoFilter Field:=2, Criteria1:=WhoFor, Operator:=xlAnd
    ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets("Summary").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
End Sub

You will need to have your Registry sheet active and a filter on.

 

[Darth_Williams]

Thank you jmacleary. I will get that into action in the morning. Thank you for your help