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.

Name	Gate Pass Required	Person Collecting
Tinashe Matambo	Yes	Mr. Matambo
Verain Mungate
Prince Mbazvo	Yes	Mrs. Maraidza

<tbody>
</tbody>

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:

Tinokura Gate Pass	Tinokura Gate Pass
This serves to confirm that:	This serves to confirm that:
"Name of Child to be picked"	"name of child to be picked"
Has been given permission	Has been given permission
to leave after his sporting	to leave after his sporting
on 2 November 2019	on 2 November 2019
Signed:________________	Signed:________________
Tinokura Housemaster	Tinokura Housemaster
Tinokura Gate Pass	Tinokura Gate Pass
This serves to confirm that:	This serves to confirm that:
"Name of Child to be picked"	"Name of Child to be picked"
Has been given permission	Has been given permission
to leave after his sporting	to leave after his sporting
on 2 November 2019	on 2 November 2019
Signed:________________	Signed:________________
Tinokura Housemaster	Tinokura Housemaster

<tbody>
</tbody>

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.