Gate pass slips for a school

[ShiftyThor]

Good day all, I am working on a sheet for a school, and would like to create an automatic selection from one sheet to another. The idea is to have one sheet with various details, and then another with a form for a gate pass. What I am aiming for is the name to appear on the gate pass, if the child has a "Yes" in a certain column.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Gate Pass Required
[/TD]
[TD]Person Collecting
[/TD]
[/TR]
[TR]
[TD]Tinashe Matambo
[/TD]
[TD]Yes
[/TD]
[TD]Mr. Matambo
[/TD]
[/TR]
[TR]
[TD]Verain Mungate
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Prince Mbazvo
[/TD]
[TD]Yes
[/TD]
[TD]Mrs. Maraidza
[/TD]
[/TR]
</tbody>[/TABLE]
This is the master file which would hold various other information, date in date out etc. However I would require the above to then select "Yes" from the gate pass column to then extract the name and place it in the forms below to be printed:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Tinokura Gate Pass
[/TD]
[TD]Tinokura Gate Pass
[/TD]
[/TR]
[TR]
[TD]This serves to confirm that:
[/TD]
[TD]This serves to confirm that:
[/TD]
[/TR]
[TR]
[TD]"Name of Child to be picked"
[/TD]
[TD]"name of child to be picked"
[/TD]
[/TR]
[TR]
[TD]Has been given permission
[/TD]
[TD]Has been given permission
[/TD]
[/TR]
[TR]
[TD]to leave after his sporting
[/TD]
[TD]to leave after his sporting
[/TD]
[/TR]
[TR]
[TD]on 2 November 2019
[/TD]
[TD]on 2 November 2019
[/TD]
[/TR]
[TR]
[TD]Signed:________________
[/TD]
[TD]Signed:________________
[/TD]
[/TR]
[TR]
[TD]Tinokura Housemaster
[/TD]
[TD]Tinokura Housemaster
[/TD]
[/TR]
[TR]
[TD]Tinokura Gate Pass
[/TD]
[TD]Tinokura Gate Pass
[/TD]
[/TR]
[TR]
[TD]This serves to confirm that:
[/TD]
[TD]This serves to confirm that:
[/TD]
[/TR]
[TR]
[TD]"Name of Child to be picked"
[/TD]
[TD]"Name of Child to be picked"
[/TD]
[/TR]
[TR]
[TD]Has been given permission
[/TD]
[TD]Has been given permission
[/TD]
[/TR]
[TR]
[TD]to leave after his sporting
[/TD]
[TD]to leave after his sporting
[/TD]
[/TR]
[TR]
[TD]on 2 November 2019
[/TD]
[TD]on 2 November 2019
[/TD]
[/TR]
[TR]
[TD]Signed:________________
[/TD]
[TD]Signed:________________
[/TD]
[/TR]
[TR]
[TD]Tinokura Housemaster
[/TD]
[TD]Tinokura Housemaster
[/TD]
[/TR]
</tbody>[/TABLE]

There will be about 4 or 6 slips printed on one paper. The problem is when using the "if" command, it will not align the rows, other than doing a serious of if commands incorporating the previous slip. There are around 300 students. The idea is to save paper, and if a column is blank in the master file, it will not be selected in the gate pass sheet, but rather select the next available "Yes"

Many thanks

 

[FormR]

Hi, you could try something like this:

Excel 2013/2016
	A	B	C
1	Name	Gate Pass Required	Person Collecting
2	Tinashe Matambo	Yes	Mr. Matambo
3	Verain Mungate
4	Prince Mbazvo	Yes	Mrs. Maraidza
5	student 1
6	student 2	Yes	Mrs. Maraidza
7	student 3
8	student 4
9	student 5
10	student 6	Yes	Mrs. Maraidza
Sheet1

Excel 2013/2016
	A	B
1	Tinokura Gate Pass	Tinokura Gate Pass
2	This serves to confirm that:	This serves to confirm that:
3	Tinashe Matambo	Prince Mbazvo
4	Has been given permission	Has been given permission
5	to leave after his sporting	to leave after his sporting
6	on 2 November 2019	on 2 November 2019
7	Signed:________________	Signed:________________
8	Tinokura Housemaster	Tinokura Housemaster
9	Tinokura Gate Pass	Tinokura Gate Pass
10	This serves to confirm that:	This serves to confirm that:
11	student 2	student 6
12	Has been given permission	Has been given permission
13	to leave after his sporting	to leave after his sporting
14	on 2 November 2019	on 2 November 2019
15	Signed:________________	Signed:________________
16	Tinokura Housemaster	Tinokura Housemaster
Sheet2

Cell Formulas
Range	Formula
A3	=INDEX(Sheet1!$A$2:$A$10,AGGREGATE(15,6,(ROW(Sheet1!$A$2:$A$10)-MIN(ROW(Sheet1!$A$2:$A$10))+1)/(Sheet1!$B$2:$B$10="Yes"),1))
A11	=INDEX(Sheet1!$A$2:$A$10,AGGREGATE(15,6,(ROW(Sheet1!$A$2:$A$10)-MIN(ROW(Sheet1!$A$2:$A$10))+1)/(Sheet1!$B$2:$B$10="Yes"),3))
B3	=INDEX(Sheet1!$A$2:$A$10,AGGREGATE(15,6,(ROW(Sheet1!$A$2:$A$10)-MIN(ROW(Sheet1!$A$2:$A$10))+1)/(Sheet1!$B$2:$B$10="Yes"),2))
B11	=INDEX(Sheet1!$A$2:$A$10,AGGREGATE(15,6,(ROW(Sheet1!$A$2:$A$10)-MIN(ROW(Sheet1!$A$2:$A$10))+1)/(Sheet1!$B$2:$B$10="Yes"),4))

The number highlighted red below is the xth row with a "Yes" in column B

=INDEX(Sheet1!$A$2:$A$10,AGGREGATE(15,6,(ROW(Sheet1!$A$2:$A$10)-MIN(ROW(Sheet1!$A$2:$A$10))+1)/(Sheet1!$B$2:$B$10="Yes"),1))

 

[ShiftyThor]

Great thank you very much. You are a legend. It seems to be working from the first few I have done.

Thank you again, truly appreciated.