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>

Roger

<colgroup><col style="width: 124px"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
[TD="bgcolor: #0c0c0c, align: center"]2016[/TD]

[TD="align: center"]NAME[/TD]
[TD="align: center"]04 Jan[/TD]
[TD="align: center"]05 Jan[/TD]
[TD="align: center"]06 Jan[/TD]
[TD="align: center"]07 Jan[/TD]

[TD="align: left"]Robbie[/TD]
[TD="bgcolor: #d8d8d8, align: center"]off

[/TD]
[TD="align: center"]in

[/TD]
[TD="align: center"]in

[/TD]
[TD="align: center"]in

[/TD]

[TD="align: left"]Chris[/TD]
[TD="align: center"]in

[/TD]
[TD="align: center"]in

[/TD]
[TD="bgcolor: #d8d8d8, align: center"]off

[/TD]
[TD="align: center"]in

[/TD]

[TD="align: left"]Natasha[/TD]
[TD="align: center"]in

[/TD]
[TD="bgcolor: #d8d8d8, align: center"]off

[/TD]
[TD="align: center"]in

[/TD]
[TD="align: center"]in

[/TD]

[TD="align: left"]Benny[/TD]
[TD="align: center"]in

[/TD]
[TD="align: center"]in

[/TD]
[TD="align: center"]in

[/TD]
[TD="align: center"]in

[/TD]

[TD="bgcolor: #d8d8d8, align: center"]off

[/TD]
[TD="align: center"]in

[/TD]
[TD="align: center"]in

[/TD]
[TD="align: center"]in

[/TD]

[TD="align: center"]Date:[/TD]
[TD="align: center"]04 Jan[/TD]

[TD="align: center"]In[/TD]
[TD="align: center"]Off[/TD]

</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


[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

2016​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

NAME​

[/TD]
[TD]

4-Jan​

[/TD]
[TD]

5-Jan​

[/TD]
[TD]

6-Jan​

[/TD]
[TD]

7-Jan​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Robbie[/TD]
[TD]

off​

[/TD]
[TD]

in​

[/TD]
[TD]

in​

[/TD]
[TD]

in​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Chris[/TD]
[TD]

in​

[/TD]
[TD]

in​

[/TD]
[TD]

off​

[/TD]
[TD]

in​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Natasha[/TD]
[TD]

in​

[/TD]
[TD]

off​

[/TD]
[TD]

in​

[/TD]
[TD]

in​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Benny[/TD]
[TD]

in​

[/TD]
[TD]

in​

[/TD]
[TD]

in​

[/TD]
[TD]

in​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]Roger[/TD]
[TD]

off​

[/TD]
[TD]

in​

[/TD]
[TD]

in​

[/TD]
[TD]

in​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

Date:​

[/TD]
[TD]

4-Jan​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

3​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

In​

[/TD]
[TD]

Off​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]Chris[/TD]
[TD]Robbie[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]Natasha[/TD]
[TD]Roger[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]Benny[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]