Training Record Table

[james_arackal]

Hi,

I have a table with all the training topics (about 50 Nos) at the left side end in a column and the names of employees on the top most row (about 300 Employees). I have date of attendence against each topic and related to each employee.

I am trying to make a second sheet with a dropdown menu of employees list, when i select the name of employee all the training attended by him should be populated.

Any idea how to do this..

thanks in advance.

James

 

[Trevor G]

Assign the employees in your Worksheet an ID number then you can use VLookup to give you the information on the second sheet

Sample

[B]Gen List[/B]


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 82px"><COL style="WIDTH: 107px"><COL style="WIDTH: 115px"><COL style="WIDTH: 75px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>EmployeeID</TD><TD>EmployeeName</TD><TD>Course</TD><TD>Date</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>S1</TD><TD>John smith</TD><TD>IT Training</TD><TD style="TEXT-ALIGN: right">12/01/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>S2</TD><TD>Bill Brown</TD><TD>PA HR Training</TD><TD style="TEXT-ALIGN: right">13/01/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>S3</TD><TD>Sarah Jones</TD><TD>IT Training</TD><TD style="TEXT-ALIGN: right">13/01/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>S4</TD><TD>Allan Ball</TD><TD>Driving Instructor</TD><TD style="TEXT-ALIGN: right">23/01/2011</TD></TR></TBODY></TABLE>
 


[COLOR=#000000][FONT=Arial][B]Excel tables to the web >> [/B][/FONT][/COLOR][URL="http://www.mrexcel.com/forum/<a href="]http://www.excel-jeanie-html.de/index.php?f=1[/URL]" target="_blank"> Excel Jeanie HTML 4

Selecting

Show

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 82px"><COL style="WIDTH: 107px"><COL style="WIDTH: 98px"><COL style="WIDTH: 75px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>EmployeeID</TD><TD>EmployeeName</TD><TD>Course</TD><TD>Date</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>S2</TD><TD>Bill Brown</TD><TD>PA HR Training</TD><TD style="TEXT-ALIGN: right">13/01/2011</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B2</TD><TD>=IF(A2>0,VLOOKUP(A2,'Gen List'!$A:$D,2,FALSE),"")</TD></TR><TR><TD>C2</TD><TD>=IF(A2>0,VLOOKUP(A2,'Gen List'!$A:$D,3,FALSE),"")</TD></TR><TR><TD>D2</TD><TD>=IF(A2>0,VLOOKUP(A2,'Gen List'!$A:$D,4,FALSE),"")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

If I am off the mark please show some sample data

 

[james_arackal]

Hi,

Thanks for this, but I do have a diffrent form of Matrix, here one employee attend more than one training topic. please see the snapshot

SL NO Topic Employee 1 Employee 2 Employee 3 Employee 4 Employee 5
1 Topic 1 01-Jan-05 01-Jan-07 01-Jan-11 01-Jan-13
2 Topic 2 02-Jan-05 02-Jan-06 02-Jan-07 02-Jan-08 02-Jan-09
4 Topic 3 03-Jan-95 03-Jan-90
5 Topic 4 04-Jan-05 04-Jan-05
6 Topic 5 05-Jan-05 05-Jan-05 05-Jan-05 05-Jan-05


From this table I want to extract the trainings attended by each employee to another sheet.

Thanks,

James

 

[Peter_SSs]

... all the training attended by him should be populated.

What data would be required? That is, what combination of SL NO, Topic and Date?

You will also get more people trying to help if you can post a better screen shot that clearly shows what is in what rows/columns. My signature block below contains 3 methods for posting small screen shots. Test them in the Test Here forum. That way, if something goes wrong, you won’t be messing up a main forum.

 

[james_arackal]

Hi Thanks for that,

Here is the screenshot of table.

Excel Workbook
	A	B	C	D	E	F	G
1	SL NO	Topic	Employee 1	Employee 2	Employee 3	Employee 4	Employee 5
2	1	Topic 1	01-Jan-05	01-Jan-07		01-Jan-11	01-Jan-13
3	2	Topic 2	02-Jan-05	02-Jan-06	02-Jan-07	02-Jan-08	02-Jan-09
4	4	Topic 3			03-Jan-95	03-Jan-90
5	5	Topic 4	04-Jan-05		04-Jan-05
6	6	Topic 5	05-Jan-05	05-Jan-05	05-Jan-05		05-Jan-05
Sheet2

Excel 2007

And here is what I am looking for when I select Employee1 in another sheet from a dropdown menu.

Excel Workbook
	B	C	D	E
4
5		Employee Name	Employee 1
6
7	Sl No.	Training	Date	Status
8	1	Topic 1	01-Jan-05
9	2	Topic 2	02-Jan-05
10	3	Topic 4	04-Jan-05
11	4	Topic 5	05-Jan-05
Personnel

Excel 2007
Thanks,

James

 

[Trevor G]

I have looked at this briefly, created two separate routines to run this. I think you should also be aware that Excel only allows you to have 255 sheets per workbook.

Sub NameWorksheet()
'Stage 1 create the sheet names based on employee names
Dim i As Integer
Application.ScreenUpdating = False
With Sheets("Employees")
Columns("A").Copy
For i = 1 To .Range("B2").End(xlToRight).Column
Sheets.Add(After:=Sheets(Sheets.Count)).Name = .Cells(1, i)
ActiveSheet.Paste
Next
End With
End Sub

Bring columns of data based on sheet names

Sub nes()
'Stage 2 copy column over that relates to employee
Dim r As Range, ws As Worksheet

On Error Resume Next
For Each r In ActiveSheet.UsedRange.Rows(1).Columns
Set ws = Nothing
Set ws = Worksheets(r.Value)
If Not ws Is Nothing Then
r.EntireColumn.Copy Destination:=ws.Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1)
End If
Next
On Error GoTo 0

End Sub

 

[Peter_SSs]

James

As I understand it you can do this with formulas if you want. The formulas are a lot simpler if you use some helper cell/columns. I don't get exactly the same results as you have shown but I'm hoping that was just a mistake with the SL NO results on your part?

See if this is any use.

In Sheet2 ..
- I've added a new column A, though you could use a column off to the right instead. If you want, this new column A can be hidden after you copy the formula in A2 down as far as you might ever need.
- I've named the range D1:H1 as Employee_List for use with the Data Validation in the Personnel sheet.

Excel Workbook
	A	B	C	D	E	F	G	H
1	0	SL NO	Topic	Employee 1	Employee 2	Employee 3	Employee 4	Employee 5
2	1	1	Topic 1	1-Jan-05	1-Jan-07		1-Jan-11	1-Jan-13
3	2	2	Topic 2	2-Jan-05	2-Jan-06	2-Jan-07	2-Jan-08	2-Jan-09
4	2	4	Topic 3			3-Jan-95	3-Jan-90
5	3	5	Topic 4	4-Jan-05		4-Jan-05
6	4	6	Topic 5	5-Jan-05	5-Jan-05	5-Jan-05		5-Jan-05
7	4
8	4
Sheet2

On the Personnel sheet ..
- A8:A? is just manually populated by dragging the sequence down as far as you might need.
- A5 and A6 are just stand-alone formulas.
- B8 is copied across to C8 and down as far as you might need.
- D8 is copied down as far as you might need.
- Again the helper column A could be hidden.

Excel Workbook
	A	B	C	D
5	1		Employee Name	Employee 1
6	4
7		SL NO	Training	Date
8	1	1	Topic 1	1-Jan-05
9	2	2	Topic 2	2-Jan-05
10	3	5	Topic 4	0-Jan-00
11	4	6	Topic 5	4-Jan-05
12	5
13	6
14	7
Personnel

#VALUE!

</td></tr></table></td></tr></table> <table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#0000ff;background-color:#ffFCF9; color:#000000; "><tr><td ><b>Data Validation in Spreadsheet</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Allow</td><td >Datas</td><td >Input 1</td><td >Input 2</td></tr><tr><td >D5</td><td >List</td><td > </td><td >=Employee_List</td><td > </td></tr></table></td></tr><tr><td ><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#0000ff;background-color:#fffcf9; color:#000000; "><tr><td ><b>Names in Formulas </b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Name</td><td >Applies to</td></tr><tr><td >D5</td><td >Employee_List</td><td >=Sheet2!$D$1:$H$1</td></tr></table></td></tr></table></td></tr></table>

 

[james_arackal]

Great Thanks Trevor and Peter,

I've worked with the Peters formula, and is almost there with a minor bug. Please have a look at Topic 4 date, it is not the expected one.

if an employee is not attended any training the topic is getting filtered but the next topic date is going to a default date. ??

Thanks in advance.

James

 

[Peter_SSs]

.. almost there with a minor bug. Please have a look at Topic 4 date, it is not the expected one.

Oops, sorry about that, not sure what I was thinking there. Try this in D8 of Personnel & copy down.

=IF(C8="","",INDEX(Sheet2!$D$2:$H$6,MATCH($A8,Sheet2!$A$2:$A$100,0),A$5))

if an employee is not attended any training the topic is getting filtered but the next topic date is going to a default date. ??

James, I'm not sure what you are saying here, so if the above change doesn't resolve this as well can you try to spell it out in more detail please.

 

[james_arackal]

Thanks Peter,

That was great and worked perfect.

James