Good afternoon,
I have learnt what I know to date by using this site and is an excellent source of advice and guidance. I am however, stuck, on how to resolve the issue I am facing with two differing formulas.
I manage a small Nursery that has two sites depending on children's age. I have attached a very small example for reference.
Basically, I will have al children data on one sheet which contains name, DOB , their attendance pattern, whether they attend Out of School or Nursery and their start date and end date. We cannot "delete" children's details when they leave for various reasons so, on that basis, the main sheet will have a mix of current children and those who have left.
Each week, I want to have 2 sheets to create the register for both sites where I add a date (Monday) that populates the current children in both sites. This is used to mark attendance and calculate staffing required.
[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD] H [/TD]
[TD] I[/TD]
[TD] J [/TD]
[TD]K[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 56"]
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <tbody>[TR="class: grid"]
[TD="width: 75, bgcolor: transparent"]Start Date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]NAme[/TD]
[TD]Dept[/TD]
[TD]DOB[/TD]
[TD]Ranked[/TD]
[TD]Leave Date[/TD]
[TD][/TD]
[TD]OOS[/TD]
[TD][/TD]
[TD]Current Children[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 56"]
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <tbody>[TR="class: grid"]
[TD="width: 75, bgcolor: #F2F2F2, align: right"]01/10/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]John
[/TD]
[TD]OOS[/TD]
[TD][TABLE="width: 54"]
<colgroup><col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <tbody>[TR]
[TD="class: xl65, width: 72, bgcolor: transparent, align: right"]01/10/2011[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jane[/TD]
[TD][/TD]
[TD]John[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 56"]
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <tbody>[TR="class: grid"]
[TD="width: 75, bgcolor: #F2F2F2, align: right"]02/10/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Zeb[/TD]
[TD]Nursery[/TD]
[TD][TABLE="width: 54"]
<colgroup><col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <tbody>[TR]
[TD="class: xl65, width: 72, bgcolor: transparent, align: right"]25/07/2012[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]John[/TD]
[TD][/TD]
[TD]Zeb[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE="width: 56"]
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <tbody>[TR="class: grid"]
[TD="width: 75, bgcolor: #F2F2F2, align: right"]03/10/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]A N[/TD]
[TD]OOS[/TD]
[TD][TABLE="width: 54"]
<colgroup><col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <tbody>[TR]
[TD="class: xl65, width: 72, bgcolor: transparent, align: right"]05/10/2013[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]4[/TD]
[TD]18/10/16[/TD]
[TD][/TD]
[TD]A N[/TD]
[TD][/TD]
[TD]Jane[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][TABLE="width: 56"]
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <tbody>[TR="class: grid"]
[TD="width: 75, bgcolor: #F2F2F2, align: right"]04/10/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Jane[/TD]
[TD]OOS[/TD]
[TD][TABLE="width: 54"]
<colgroup><col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <tbody>[TR]
[TD="class: xl65, width: 72, bgcolor: transparent, align: right"]05/08/2011[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
My table above is to show where I would like some help (the original sheet will be across more than one tab). In column I, , I can look up those children who are due to be in the OOS club. (and a similar formula for Nursery but not shown here)
IFERROR(INDEX($C$3:$C$6, MATCH(SMALL(IF($I$2=$D$3:$D$6, $F$3:$F$6, ""), ROW(A1)), IF($I$2=$D$3:$D$6, $F$3:$F$6, ""),0)),"")
where I2 is called OOS and finds those children who have OOS in column D. It also sorts the results by the helper column as the children are listed on the register in age order. This formula works.... but I am sure it could be much simpler if I could do so.
The second formula in column K allows me to return those children who are still at the Nursery using this formula
IFERROR(INDEX($C$3:$C$6,SMALL(IF(NOT(ISBLANK($G$3:$G$6)),"",ROW($G$3:$G$6)-ROW($K$3)+1),ROW(K3)-ROW($K$3)+1)),"")
What I really need help with is combining them both so that I can:
Search for current children (second formula) and also sort those current children by age and what site (OOS or Nursery) -first formula.
Sorry for the long winded explanation and I hope this makes sense but please let me know if it doesn't.
Regards
Terry
I have learnt what I know to date by using this site and is an excellent source of advice and guidance. I am however, stuck, on how to resolve the issue I am facing with two differing formulas.
I manage a small Nursery that has two sites depending on children's age. I have attached a very small example for reference.
Basically, I will have al children data on one sheet which contains name, DOB , their attendance pattern, whether they attend Out of School or Nursery and their start date and end date. We cannot "delete" children's details when they leave for various reasons so, on that basis, the main sheet will have a mix of current children and those who have left.
Each week, I want to have 2 sheets to create the register for both sites where I add a date (Monday) that populates the current children in both sites. This is used to mark attendance and calculate staffing required.
[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD] H [/TD]
[TD] I[/TD]
[TD] J [/TD]
[TD]K[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 56"]
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <tbody>[TR="class: grid"]
[TD="width: 75, bgcolor: transparent"]Start Date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]NAme[/TD]
[TD]Dept[/TD]
[TD]DOB[/TD]
[TD]Ranked[/TD]
[TD]Leave Date[/TD]
[TD][/TD]
[TD]OOS[/TD]
[TD][/TD]
[TD]Current Children[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 56"]
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <tbody>[TR="class: grid"]
[TD="width: 75, bgcolor: #F2F2F2, align: right"]01/10/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]John
[/TD]
[TD]OOS[/TD]
[TD][TABLE="width: 54"]
<colgroup><col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <tbody>[TR]
[TD="class: xl65, width: 72, bgcolor: transparent, align: right"]01/10/2011[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jane[/TD]
[TD][/TD]
[TD]John[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 56"]
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <tbody>[TR="class: grid"]
[TD="width: 75, bgcolor: #F2F2F2, align: right"]02/10/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Zeb[/TD]
[TD]Nursery[/TD]
[TD][TABLE="width: 54"]
<colgroup><col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <tbody>[TR]
[TD="class: xl65, width: 72, bgcolor: transparent, align: right"]25/07/2012[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]John[/TD]
[TD][/TD]
[TD]Zeb[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE="width: 56"]
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <tbody>[TR="class: grid"]
[TD="width: 75, bgcolor: #F2F2F2, align: right"]03/10/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]A N[/TD]
[TD]OOS[/TD]
[TD][TABLE="width: 54"]
<colgroup><col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <tbody>[TR]
[TD="class: xl65, width: 72, bgcolor: transparent, align: right"]05/10/2013[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]4[/TD]
[TD]18/10/16[/TD]
[TD][/TD]
[TD]A N[/TD]
[TD][/TD]
[TD]Jane[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][TABLE="width: 56"]
<colgroup><col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <tbody>[TR="class: grid"]
[TD="width: 75, bgcolor: #F2F2F2, align: right"]04/10/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Jane[/TD]
[TD]OOS[/TD]
[TD][TABLE="width: 54"]
<colgroup><col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <tbody>[TR]
[TD="class: xl65, width: 72, bgcolor: transparent, align: right"]05/08/2011[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
My table above is to show where I would like some help (the original sheet will be across more than one tab). In column I, , I can look up those children who are due to be in the OOS club. (and a similar formula for Nursery but not shown here)
IFERROR(INDEX($C$3:$C$6, MATCH(SMALL(IF($I$2=$D$3:$D$6, $F$3:$F$6, ""), ROW(A1)), IF($I$2=$D$3:$D$6, $F$3:$F$6, ""),0)),"")
where I2 is called OOS and finds those children who have OOS in column D. It also sorts the results by the helper column as the children are listed on the register in age order. This formula works.... but I am sure it could be much simpler if I could do so.
The second formula in column K allows me to return those children who are still at the Nursery using this formula
IFERROR(INDEX($C$3:$C$6,SMALL(IF(NOT(ISBLANK($G$3:$G$6)),"",ROW($G$3:$G$6)-ROW($K$3)+1),ROW(K3)-ROW($K$3)+1)),"")
What I really need help with is combining them both so that I can:
Search for current children (second formula) and also sort those current children by age and what site (OOS or Nursery) -first formula.
Sorry for the long winded explanation and I hope this makes sense but please let me know if it doesn't.
Regards
Terry