Creating a daily register based on a different worksheet

[Collington]

Hey folks,

Can you help me with a question for my school?
We have children who attend breakfast club. On one sheet we list ALL the children and how many days that term will attend breakfast club (e.g. 5 Mondays, 6 Tuesdays) - this is so we can bill correctly. I want this to automatically filter through to a register sheet so we can tick the children off each day. <br><br>

<b>1. The sheet from which we do the billing</b>

<style type="text/css">
.tg {border-collapse:collapse;border-spacing:0;}
.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:black;}
.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:black;}
.tg .tg-fymr{font-weight:bold;border-color:inherit;text-align:left;vertical-align:top}
.tg .tg-0pky{border-color:inherit;text-align:left;vertical-align:top}
</style>
<table class="tg">
<tr>
<th class="tg-0lax">Name</th>
<th class="tg-0lax">Year Group</th>
<th class="tg-0lax">Monday</th>
<th class="tg-0lax">Tuesday</th>
<th class="tg-0lax">Wednesday</th>
<th class="tg-0lax">Thursday</th>
<th class="tg-0lax">Friday</th>
</tr>
<tr>
<td class="tg-0lax">Bob Smith</td>
<td class="tg-0lax">1</td>
<td class="tg-0lax"></td>
<td class="tg-0lax">5</td>
<td class="tg-0lax"></td>
<td class="tg-0lax">7</td>
<td class="tg-0lax">7</td>
</tr>
<tr>
<td class="tg-0lax">Nancy Drew</td>
<td class="tg-0lax">4</td>
<td class="tg-0lax">6</td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
</tr>
<tr>
<td class="tg-0lax">Frank Longbottom</td>
<td class="tg-0lax">6</td>
<td class="tg-0lax">6</td>
<td class="tg-0lax">5</td>
<td class="tg-0lax">7</td>
<td class="tg-0lax">7</td>
<td class="tg-0lax">7</td>
</tr>
<tr>
<td class="tg-0lax">Zachary Zoo</td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax">5</td>
<td class="tg-0lax">5</td>
<td class="tg-0lax"></td>
</tr>
<tr>
<td class="tg-0lax">Magnus Magnuson</td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax"></td>
<td class="tg-0lax">7</td>
<td class="tg-0lax">7</td>
<td class="tg-0lax"></td>
</tr>
</table>

<b>2. What I would LOVE the attendance register to look like!</b>

<style type="text/css">
.tg {border-collapse:collapse;border-spacing:0;}
.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:black;}
.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:black;}
.tg .tg-fymr{font-weight:bold;border-color:inherit;text-align:left;vertical-align:top}
.tg .tg-0pky{border-color:inherit;text-align:left;vertical-align:top}
</style>
<table class="tg">
<tr>
<th class="tg-fymr">Monday</th>
<th class="tg-fymr">Tuesday</th>
<th class="tg-fymr">Wednesday</th>
<th class="tg-fymr">Thursday</th>
<th class="tg-fymr">Friday</th>
</tr>
<tr>
<td class="tg-0pky">Nancy Drew</td>
<td class="tg-0pky">Bob Smith</td>
<td class="tg-0pky">Frank Longbottom</td>
<td class="tg-0pky">Bob Smith</td>
<td class="tg-0pky">Bob Smith</td>
</tr>
<tr>
<td class="tg-0pky">Frank Longbottom</td>
<td class="tg-0pky">Frank Longbottom</td>
<td class="tg-0pky">Zachary Zoo</td>
<td class="tg-0pky">Frank Longbottom</td>
<td class="tg-0pky">Frank Longbottom</td>
</tr>
<tr>
<td class="tg-0pky"></td>
<td class="tg-0pky"></td>
<td class="tg-0pky">Magnus Magnuson</td>
<td class="tg-0pky">Zachary Zoo</td>
<td class="tg-0pky"></td>
</tr>
<tr>
<td class="tg-0pky"></td>
<td class="tg-0pky"></td>
<td class="tg-0pky"></td>
<td class="tg-0pky">Magnus Magnuson</td>
<td class="tg-0pky"></td>
</tr>
<tr>
<td class="tg-0pky"></td>
<td class="tg-0pky"></td>
<td class="tg-0pky"></td>
<td class="tg-0pky"></td>
<td class="tg-0pky"></td>
</tr>
</table>
<br><br>

Please note: the particular numbers in the days column are irrelevant to this. If <u>any</u> number is in "Monday" that kid should be on the Monday register.<br><br>
<b> awkward addition</b> You can see we're also listing year groups. This is because we are a split site and kids in years 1-3 are in one building, and 4-6 in another. So in a dreamworld it would create two different registers, the first one pulling the names of the younger children only, the second of the older children only.... but I might be asking too much with that one?<br><br>

<b>As always, you folks are amazing. Thank you so much for your help!!</b>

 

[Fluff]

How about

Book1
	A	B	C	D	E
1	Years 1-3
2	Monday	Tuesday	Wednesday	Thursday	Friday
3	Nancy Drew	Bob Smith	Magnus Magnuson	Bob Smith	Bob Smith
4				Magnus Magnuson
5
6
7
8
9	Years 4-6
10	Monday	Tuesday	Wednesday	Thursday	Friday
11	Frank Longbottom	Frank Longbottom	Frank Longbottom	Frank Longbottom	Frank Longbottom
12			Zachary Zoo	Zachary Zoo
13
Sheet3

Cell Formulas
Range	Formula
A3	=IFERROR(INDEX(Sheet2!$A$2:$A$6,AGGREGATE(15,6,(ROW(Sheet2!$A$2:$A$6)-ROW(Sheet2!$A$2)+1)/((Sheet2!$B$2:$B$6<=3)*(Sheet2!C$2:C$6<>"")),ROWS($A$2:$A2))),"")
A11	=IFERROR(INDEX(Sheet2!$A$2:$A$6,AGGREGATE(15,6,(ROW(Sheet2!$A$2:$A$6)-ROW(Sheet2!$A$2)+1)/((Sheet2!$B$2:$B$6>3)*(Sheet2!C$2:C$6<>"")),ROWS($A$11:$A11))),"")

 

[Collington]

Fluff,

This is the second time you've answered one of my absurd queries. Thank you so much! I owe you a beer, coffee, or other beverage of your choice! THANK YOU!

Lisa

 

[Fluff]

You're welcome & thanks for the feedback