Auto Training Tracker w/MS Forms

[Nic0la]

I am trying to create an auto training tracker to help track when my students complete a task/requirement.

The idea is they fillout an online MS Form, I take those responces into my spreadsheet and it automaticly populates the tracker with the date they completed the task. I have tried using XLOOKUP and FILTER but can't seem to get the formulas to ignore blank cells and keep searching. This is needed as students might fill out the form multiple times for other training before they do 'task 1' for example. I also need the formula to always take the most recent date for that task as students will have to do it multiple times to stay 'current'.
To keep things more simple I will always paste the downloaded forms data in order and the tracker should use their student number to match their records.

Any help would be greatly appreciated. Let me know if you have any questions.

 

[Snakehips]

@Nic0la Welcome. Does something like this help?
Use the top left copy icon if you wish to paste the mini-sheet into a sheet.

AutoTraining.xlsm
	A	B	C	D	E	F
1	Surname	S Number	Task 1	Task 2	Task 3	Task 4
2	Smith A	30105	5/5/24	5/27/24
3	Price	30108	4/6/24	3/6/23
4	Jones	10109
5	Smith B	30103				4/22/24
6	Soap J	30104
7
8
9
Tracker

Cell Formulas
Range	Formula
C2:F9	C2	=LET(d, MAX(('Form Responses'!$D2:$D10=$B2)*('Form Responses'!E2:E10)),IF(OR($B2="",d=0),"",d))

I/D	Completion Time	Surname	S Number	Task 1	Task 2	Task 3	Task 4
1		Smith	30105	5/1/24
2		Price	30108		3/6/23
3		Smith	30105	5/5/24
4		Price	30108	4/6/24
5		Smith	30105		5/27/24
6		Smith	30103				4/22/24
7		Soap	30104

 

[Nic0la]

@Nic0la Welcome. Does something like this help?
Use the top left copy icon if you wish to paste the mini-sheet into a sheet.

AutoTraining.xlsm
	A	B	C	D	E	F
1	Surname	S Number	Task 1	Task 2	Task 3	Task 4
2	Smith A	30105	5/5/24	5/27/24
3	Price	30108	4/6/24	3/6/23
4	Jones	10109
5	Smith B	30103				4/22/24
6	Soap J	30104
7
8
9
Tracker

Cell Formulas
Range	Formula
C2:F9	C2	=LET(d, MAX(('Form Responses'!$D2:$D10=$B2)*('Form Responses'!E2:E10)),IF(OR($B2="",d=0),"",d))

I/D	Completion Time	Surname	S Number	Task 1	Task 2	Task 3	Task 4
1		Smith	30105	5/1/24
2		Price	30108		3/6/23
3		Smith	30105	5/5/24
4		Price	30108	4/6/24
5		Smith	30105		5/27/24
6		Smith	30103				4/22/24
7		Soap	30104

Perfect! That has worked (no idea how haha), thank you so much!

 

[Nic0la]

Can anyone tell me a formula for using conditional formatting to highlight cells where the returned values are out of date?
For example, Task 2 is only valid for one year, meaning students need to do the training and fill out the form each year. I would like the cell to be highlighted yellow in the month it goes out of date and red after this date if they haven't updated the tracker/training. If the cell is blank, I would like no conditional formatting.

Any help is greatly appreciated!

 

[Snakehips]

Are you wanting to apply one year validity period to all tasks or will the period vary by task?

 

[Nic0la]

Are you wanting to apply one year validity period to all tasks or will the period vary by task?

It will vary by task.

Apologies I cannot get XL2BB to run on my work laptop.

 

[Snakehips]

Then you will need something like this where the validity period (yrs) is defined.

AutoTraining.xlsm
	A	B	C	D	E	F	G	H
1				Task Validity (Yrs)>	1	99	2	1
2	I/D	Completion Time	Surname	S Number	Task 1	Task 2	Task 3	Task 4
3	1		Smith	30105	6/1/23
4	2		Price	30108		7/2/22
5	3		Smith	30105	5/3/23
6	4		Price	30108	4/6/23		7/2/22
7	5		Smith	30105		5/3/23
8	6		Smith	30103				7/4/23
9	7		Soap	30104			6/1/22
10
Form Responses

Then hopefull, give or take a day the below should do it?

AutoTraining.xlsm
	A	B	C	D	E	F
1	Surname	S Number	Task 1	Task 2	Task 3	Task 4
2	Smith A	30105	01/06/2023	03/05/2023
3	Price	30108	06/04/2023	02/07/2022	02/07/2022
4	Jones	10109
5	Smith B	30103				04/07/2023
6	Soap J	30104			01/06/2022
7
8
9
10
Tracker

Cell Formulas
Range	Formula
C2:F9	C2	=LET(d, MAX(('Form Responses'!$D3:$D11=$B2)*('Form Responses'!E3:E11)),IF(OR($B2="",d=0),"",d))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:F10	Expression	=AND(ISNUMBER(C2),(TODAY()-(365*'Form Responses'!E$1))>=C2)	text	YES
C2:F10	Expression	=AND(ISNUMBER(C2),(TODAY()-((365*'Form Responses'!E$1)-30))>=C2)	text	YES