Automation for training sheet

[David Clark]

Good day all,

I am an officer with a Volunteer Fire Department and I am looking for some help building automation into my training files. I have an excel book with 50+ sheets for each active member of the department. This file is used to keep records of all mandatory training needed to remain an active riding member in the department. In the 2 images uploaded, one is the single sheet per member and the second is a full list of each member and trainings.

What I am trying to do is pull the data from the "Required Certifications" members sheet to the "testing" sheet and then it would place an X on the completed training once I enter anything into the information date section. I will then be using the "training" sheet to make a pivot table(no help needed with pivot table) that I can then send to the membership to show where we all stand at the 6 month mark of the year for completions.

If I can get a formula for one of the categories I believe I can change the needed formula to also pull from the others.

Thank you in advance and if this is too much of an ask or hard to do I can revert to how it used to be done by just adding the X myself like my predecessor did.

 

[Snakehips]

@Snakehips Im always up for learning something new, Like I said though I have no knowledge of VBA, if that doesnt bother you Im in for the learning!

Let's go for it. Not a lot to be lost if it doesn't suit your needs.

Can you confirm or correct the following assumptions;
The name in D6 will match a name in Testing Sheet column A
The Date entry cells (Red) are 2 columns merged.

You will make single entries or clearances in a member's certification sheet.
Generally, no multi-cell pasting or contents clearing.
Making an entry into a Date cell to place an X in the appropriate cell of Testing Sheet.
Clearing an entry from a Date cell to remove an X from the appropriate cell of Testing Sheet.
X in column S only if 2 or more Elective Classes are dated.

Do you currently see the Developer Tab in your quick access toolbar?

 

[David Clark]

Let's go for it. Not a lot to be lost if it doesn't suit your needs.

Can you confirm or correct the following assumptions;
The name in D6 will match a name in Testing Sheet column A
The Date entry cells (Red) are 2 columns merged.

You will make single entries or clearances in a member's certification sheet.
Generally, no multi-cell pasting or contents clearing.
Making an entry into a Date cell to place an X in the appropriate cell of Testing Sheet.
Clearing an entry from a Date cell to remove an X from the appropriate cell of Testing Sheet.
X in column S only if 2 or more Elective Classes are dated.

Do you currently see the Developer Tab in your quick access toolbar?

@Snakehips I apologize for letting your question sit for so long. I had some items in life happen and missed the email saying you had replied to this thread.
Currently the name in D6 does NOT match the sheet name, D6 is First / last and sheet is Last, First(however that is an easy change)

Yes the date entry cells in red are merged.
Correct I would make the change to the member sheet when a class / training is completed.
Correct- no multi-cell pasting is usually done
Correct- Once the date is populated in the member sheet then an X would go into the "testing" sheet(name would change most likely later)
Correct- When a cell with a date in it on the members sheet it would then clear the X from the testing sheet
Correct- X in column S only if 2 or more elective classes are dated.

 

[Snakehips]

@David Clark Below you will find code that may or not be of value.
Also are my testing sheets. You can test using these or maybe on a backed-up copy of a couple of your own sheets.
Copy the code. Open the vba editor and double click on the test member sheet (s). Post the code into the sheet's code pane.

The code should then auto-run as and when there is a change in the sheet, and reflect any added or removed dates with X's in the 'Testing Sheet'.
As it is stands, It assumes the name in the member sheet can be found in the Testing Sheet listing.?

You will not require to have any formulas in the testing sheet for this to function.

If it has any ongoing potential then we may need to discus the best way to code for 50 sheets?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Integer, x As String
'if not valid date entry column then ignore
If Not (Target.Column = 7 Or Target.Column = 18) Then Exit Sub
'check that entry row is valid
Select Case Target.Row
        Case Is < 16, Is > 34, 25, 26, 27    ' then is not a valid entry row
        Exit Sub  'not valid so ignore
        
        Case Is < 25 'is required training entry
        'set c to be the column for the class in question
        c = Target.Row - (14 + (Target.Column = 18) * 9)
        
        If Target.Cells(1, 1).Value > "" Then x = "X" 'if date cell has value
        Case Is > 27 'is Elective Training entry
         'check if there are at least 2 Elective trainings complete
        If WorksheetFunction.CountA(Range("G28:H34")) + WorksheetFunction.CountA(Range("R28:S34")) > 1 Then
            c = 19 'always column S
            x = "X"
            Else
            c = 19 'Column S
            s = "" 'not X since too few classes complete
        End If
       
   End Select
       
   On Error Resume Next   'ignore any error message if name not found
 'match name in row r
r = WorksheetFunction.Match(Range("D6"), Sheets("Testing Sheet").Range("A1:A100"), 0)
'If there was no match r will be 0
If r = 0 Then
    MsgBox "Cannot find matching name in Testing Sheet.  Please check and try again."
    Target.Select
    On Error GoTo 0  'reset error default
    Exit Sub
End If
'Enter  x in Testing Sheet
Beep
Sheets("Testing Sheet").Cells(r, c) = x  '**** Rename sheet if required ***
On Error GoTo 0  'reset error default
       
End Sub

AutoTraining2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Name	Rqd-1	Rqd-2	Rqd-3	Rqd-4	Rqd-5	Rqd-6	Rqd-7	Rqd-8	Rqd-9	Rqd-10	Rqd-11	Rqd-12	Rqd-13	Rqd-14	Rqd-15	Rqd-16	Rqd-17	Elective
2	Member 1
3	Member 2
4	Member 3
5	Member 4
6	Sam Fireman	X												X					X
7	Member 6
8	Member 7
9	Member 8
10	Member 9
11	John Doe						X			X		X						X
12	Member 11
13	Member 12
14	Member 13
15	Member 14
16	Member 15
17	Member 16
18	Member 17
Testing Sheet

AutoTraining2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1
2
3
4
5
6			Name:	John Doe
7
8
9
10
11
12
13
14								Required
15		Topic					Date		Instructor				Topic					Date				Instructor
16		Rqd-1											Rqd-10
17		Rqd-2											Rqd-11					1/1/23
18		Rqd-3											Rqd-12
19		Rqd-4											Rqd-13
20		Rqd-5											Rqd-14
21		Rqd-6					1/1/23						Rqd-15
22		Rqd-7											Rqd-16
23		Rqd-8											Rqd-17					1/1/23
24		Rqd-9					1/1/23
25
26								Elective (Needs 2)
27		Topic					Date		Instructor				Topic					Date				Instructor
28		Elect-1											Elect-8
29		Elect-2											Elect-9
30		Elect-3											Elect-10					1/1/23
31		Elect-4											Elect-11
32		Elect-5											Elect-12
33		Elect-6											Elect-13
34		Elect-7											Elect-14
35
Doe John

AutoTraining2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1
2
3
4																		1/1/23
5
6			Name:	Sam Fireman
7
8
9
10
11
12
13
14								Required
15		Topic					Date		Instructor				Topic					Date				Instructor
16		Rqd-1					1/1/23						Rqd-10
17		Rqd-2											Rqd-11
18		Rqd-3											Rqd-12
19		Rqd-4											Rqd-13					1/1/23
20		Rqd-5											Rqd-14
21		Rqd-6											Rqd-15
22		Rqd-7											Rqd-16
23		Rqd-8											Rqd-17
24		Rqd-9
25															3
26								Elective (Needs 2)					3
27		Topic					Date		Instructor				Topic		3			Date				Instructor
28		Elect-1					1/1/23						Elect-8
29		Elect-2											Elect-9
30		Elect-3											Elect-10
31		Elect-4											Elect-11
32		Elect-5											Elect-12					1/1/23
33		Elect-6											Elect-13
34		Elect-7											Elect-14
35
Fireman Sam

 

[David Clark]

@David Clark Below you will find code that may or not be of value.
Also are my testing sheets. You can test using these or maybe on a backed-up copy of a couple of your own sheets.
Copy the code. Open the vba editor and double click on the test member sheet (s). Post the code into the sheet's code pane.

The code should then auto-run as and when there is a change in the sheet, and reflect any added or removed dates with X's in the 'Testing Sheet'.
As it is stands, It assumes the name in the member sheet can be found in the Testing Sheet listing.?

You will not require to have any formulas in the testing sheet for this to function.

If it has any ongoing potential then we may need to discus the best way to code for 50 sheets?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Integer, x As String
'if not valid date entry column then ignore
If Not (Target.Column = 7 Or Target.Column = 18) Then Exit Sub
'check that entry row is valid
Select Case Target.Row
        Case Is < 16, Is > 34, 25, 26, 27    ' then is not a valid entry row
        Exit Sub  'not valid so ignore
       
        Case Is < 25 'is required training entry
        'set c to be the column for the class in question
        c = Target.Row - (14 + (Target.Column = 18) * 9)
       
        If Target.Cells(1, 1).Value > "" Then x = "X" 'if date cell has value
        Case Is > 27 'is Elective Training entry
         'check if there are at least 2 Elective trainings complete
        If WorksheetFunction.CountA(Range("G28:H34")) + WorksheetFunction.CountA(Range("R28:S34")) > 1 Then
            c = 19 'always column S
            x = "X"
            Else
            c = 19 'Column S
            s = "" 'not X since too few classes complete
        End If
      
   End Select
      
   On Error Resume Next   'ignore any error message if name not found
 'match name in row r
r = WorksheetFunction.Match(Range("D6"), Sheets("Testing Sheet").Range("A1:A100"), 0)
'If there was no match r will be 0
If r = 0 Then
    MsgBox "Cannot find matching name in Testing Sheet.  Please check and try again."
    Target.Select
    On Error GoTo 0  'reset error default
    Exit Sub
End If
'Enter  x in Testing Sheet
Beep
Sheets("Testing Sheet").Cells(r, c) = x  '**** Rename sheet if required ***
On Error GoTo 0  'reset error default
      
End Sub

Thanks for the help with this even though Im a little slow responding.

I added VBA to my excel file, Opened Visual Basic and then double clicked on the name of the testing sheet and added the formula you provided to it. It did not do anything, I then looked at your test sheets and made sure that the spelling for the sheet name, the name listed in column A and the name listed inside the sheet all matched. That still did not mark any changes on the "testing" sheet. I then noticed that the name in the sheet you provided is listed in Row 6 Column Dm however on my sheets it is also merged from D through O. I un merged it in one of the sheets and it also did not do anything. So as a lets see if I loaded on the wrong sheet, I then went to each individual sheet and added the formula you provided. Also there was no change to the "Testing" sheet.

Attached is a photo of one of the sheets used (the one for myself) The sheets are named the same as the name on the sheet, Also is a photo of the Testing sheet. That sheet is named Testing but can be changed to anything if needed.