Rota - Who's in on a specific date function

[aaronrainb]

Hi all!

I'm having an issue working out how to summarise a who's in today function for a rota i'm working on. I've put a rough version up in the following location:

https://docs.google.com/spreadsheets/d/1sjz7oDMGAeKEaQvIZU4ZsF4XcAJ95tHfM67NBoGRyUM/edit?usp=sharing

As seen in the link above and below, i'd like it to look at the date in the box above and then show who's in and off. From looking online it seems to be a mix of index, match and small and working with arrays? The array side and the Small function i've unexperienced in and I couldn't find an example that seemed to match my specific situation. Any help would be wonderful, i'm working with google sheets but I assume a standard excel solution would work for it? Many thanks!


<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

2016
NAME	04 Jan	05 Jan	06 Jan	07 Jan
Robbie	off	in	in	in
Chris	in	in	off	in
Natasha	in	off	in	in
Benny	in	in	in	in
Roger	off	in	in	in
Date:	04 Jan
In	Off

<colgroup><col style="width: 124px"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
</tbody>

 

[Weazel]

maybe somthing like...


A12=IF(ROWS(A$12:A12)>A$10,"",INDEX($A$3:$A$7,SMALL(IF($B$2:$E$2=$B$9,IF($B$3:$E$7=A$11,ROW(B$3:B$7)-ROW(B$3)+1)),ROWS(A$12:A12)))) control shift enter

Row\Col	A​	B​	C​	D​	E​
1​	2016​
2​	NAME​	4-Jan​	5-Jan​	6-Jan​	7-Jan​
3​	Robbie	off​	in​	in​	in​
4​	Chris	in​	in​	off​	in​
5​	Natasha	in​	off​	in​	in​
6​	Benny	in​	in​	in​	in​
7​	Roger	off​	in​	in​	in​
8​
9​	Date:​	4-Jan​
10​	3​	2​
11​	In​	Off​
12​	Chris	Robbie
13​	Natasha	Roger
14​	Benny
15​
16​

<tbody>
</tbody>