A | B | C | |
1 | Header1 | header2 | header3 |
2 | data | data | data |
3 | data | data | data |
4 | data | data | data |
5 | data | data | data |
6 | data | data | data |
A | B | C | D | E | F | |
1 | 1/01/2019 | 02/01/2019 | 03/01/2019 | 04/01/2019 | ETC..... | |
2 | STAFF 1 | D-o | O-d | H-r | R | |
3 | STAFF 2 | O-d | H-r | R | W | |
4 | STAFF 3 | H-r | R | E | ||
5 | STAFF 4 | R | D | D | ||
6 | STAFF 5 | D | H-r | |||
7 | ||||||
8 | STAFF 1 | swap | ||||
9 | STAFF 2 | N/A | ||||
10 | STAFF 3 | N/A | ||||
11 | STAFF 4 | |||||
12 | STAFF 5 |
1 | In cell B30 I want to input a date. | |||||||||
2 | Under that I want to see a staff list based on: | |||||||||
3 | Blank Cells and those containing H or O(left most letter) from the top table B2:E6 | |||||||||
combined with cells NOT containing NA in the lower tableB8:E12 | ||||||||||
Info | W,D,R and E are shifts fulfilled be those staff. H is holiday | |||||||||
It would need to apply to all cells in both tables. | ||||||||||
List would expand/contract as rota and/or date changes and be blank when no date is present |
Book1 | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | 1/1/2019 | 1/2/2019 | 1/3/2019 | 1/4/2019 | |||
2 | STAFF 1 | D-o | O-d | H-r | R | ||
3 | STAFF 2 | O-d | H-r | R | W | ||
4 | STAFF 3 | H-r | R | E | |||
5 | STAFF 4 | R | D | D | |||
6 | STAFF 5 | D | H-r | ||||
7 | |||||||
8 | STAFF 1 | swap | |||||
9 | STAFF 2 | N/A | |||||
10 | STAFF 3 | N/A | |||||
11 | STAFF 4 | ||||||
12 | STAFF 5 | ||||||
13 | |||||||
14 | |||||||
27 | |||||||
28 | |||||||
29 | |||||||
30 | 1/1/2019 | ||||||
31 | STAFF 5 | ||||||
32 | |||||||
33 | |||||||
34 | |||||||
35 | |||||||
36 | |||||||
37 | |||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B31 | {=IF(ROWS($B$31:B31)>SUM(((LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)="H")+(LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)="O")+(LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)=""))*(LEFT(INDEX($B$8:$E$12,0,MATCH($B$30,$B$1:$E$1,0)),1)="")),"",INDEX($A$2:$A$6,SMALL(IF(((LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)="H")+(LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)="O")+(LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)=""))*(LEFT(INDEX($B$8:$E$12,0,MATCH($B$30,$B$1:$E$1,0)),1)=""),ROW($A$2:$A$6)-ROW($A$2)+1),ROWS($B$31:B31))))} | |
B32 | {=IF(ROWS($B$31:B32)>SUM(((LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)="H")+(LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)="O")+(LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)=""))*(LEFT(INDEX($B$8:$E$12,0,MATCH($B$30,$B$1:$E$1,0)),1)="")),"",INDEX($A$2:$A$6,SMALL(IF(((LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)="H")+(LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)="O")+(LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)=""))*(LEFT(INDEX($B$8:$E$12,0,MATCH($B$30,$B$1:$E$1,0)),1)=""),ROW($A$2:$A$6)-ROW($A$2)+1),ROWS($B$31:B32))))} | |
B33 | {=IF(ROWS($B$31:B33)>SUM(((LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)="H")+(LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)="O")+(LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)=""))*(LEFT(INDEX($B$8:$E$12,0,MATCH($B$30,$B$1:$E$1,0)),1)="")),"",INDEX($A$2:$A$6,SMALL(IF(((LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)="H")+(LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)="O")+(LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)=""))*(LEFT(INDEX($B$8:$E$12,0,MATCH($B$30,$B$1:$E$1,0)),1)=""),ROW($A$2:$A$6)-ROW($A$2)+1),ROWS($B$31:B33))))} | |
B34 | {=IF(ROWS($B$31:B34)>SUM(((LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)="H")+(LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)="O")+(LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)=""))*(LEFT(INDEX($B$8:$E$12,0,MATCH($B$30,$B$1:$E$1,0)),1)="")),"",INDEX($A$2:$A$6,SMALL(IF(((LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)="H")+(LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)="O")+(LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)=""))*(LEFT(INDEX($B$8:$E$12,0,MATCH($B$30,$B$1:$E$1,0)),1)=""),ROW($A$2:$A$6)-ROW($A$2)+1),ROWS($B$31:B34))))} | |
B35 | {=IF(ROWS($B$31:B35)>SUM(((LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)="H")+(LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)="O")+(LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)=""))*(LEFT(INDEX($B$8:$E$12,0,MATCH($B$30,$B$1:$E$1,0)),1)="")),"",INDEX($A$2:$A$6,SMALL(IF(((LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)="H")+(LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)="O")+(LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)=""))*(LEFT(INDEX($B$8:$E$12,0,MATCH($B$30,$B$1:$E$1,0)),1)=""),ROW($A$2:$A$6)-ROW($A$2)+1),ROWS($B$31:B35))))} | |
B36 | {=IF(ROWS($B$31:B36)>SUM(((LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)="H")+(LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)="O")+(LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)=""))*(LEFT(INDEX($B$8:$E$12,0,MATCH($B$30,$B$1:$E$1,0)),1)="")),"",INDEX($A$2:$A$6,SMALL(IF(((LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)="H")+(LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)="O")+(LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)=""))*(LEFT(INDEX($B$8:$E$12,0,MATCH($B$30,$B$1:$E$1,0)),1)=""),ROW($A$2:$A$6)-ROW($A$2)+1),ROWS($B$31:B36))))} | |
B37 | {=IF(ROWS($B$31:B37)>SUM((((--(LEFT(B$2:B$6,1)="H"))+(--(LEFT(B$2:B$6,1)="O")))+(B$2:B$6=""))*(B$8:B$12<>"N/A")),"",INDEX($A$2:$A$6,SMALL(IF((((--(LEFT(B$2:B$6,1)="H"))+(--(LEFT(B$2:B$6,1)="O")))+(B$2:B$6=""))*(B$8:B$12<>"N/A"),ROW($A$2:$A$6)-ROW($A$2)+1),ROWS($B$31:B37))))} | |
C37 | {=IF(ROWS($B$31:C37)>SUM((((--(LEFT(C$2:C$6,1)="H"))+(--(LEFT(C$2:C$6,1)="O")))+(C$2:C$6=""))*(C$8:C$12<>"N/A")),"",INDEX($A$2:$A$6,SMALL(IF((((--(LEFT(C$2:C$6,1)="H"))+(--(LEFT(C$2:C$6,1)="O")))+(C$2:C$6=""))*(C$8:C$12<>"N/A"),ROW($A$2:$A$6)-ROW($A$2)+1),ROWS($B$31:C37))))} | |
D37 | {=IF(ROWS($B$31:D37)>SUM((((--(LEFT(D$2:D$6,1)="H"))+(--(LEFT(D$2:D$6,1)="O")))+(D$2:D$6=""))*(D$8:D$12<>"N/A")),"",INDEX($A$2:$A$6,SMALL(IF((((--(LEFT(D$2:D$6,1)="H"))+(--(LEFT(D$2:D$6,1)="O")))+(D$2:D$6=""))*(D$8:D$12<>"N/A"),ROW($A$2:$A$6)-ROW($A$2)+1),ROWS($B$31:D37))))} | |
E37 | {=IF(ROWS($B$31:E37)>SUM((((--(LEFT(E$2:E$6,1)="H"))+(--(LEFT(E$2:E$6,1)="O")))+(E$2:E$6=""))*(E$8:E$12<>"N/A")),"",INDEX($A$2:$A$6,SMALL(IF((((--(LEFT(E$2:E$6,1)="H"))+(--(LEFT(E$2:E$6,1)="O")))+(E$2:E$6=""))*(E$8:E$12<>"N/A"),ROW($A$2:$A$6)-ROW($A$2)+1),ROWS($B$31:E37))))} | |
Press CTRL+SHIFT+ENTER to enter array formulas. |
=IF(ROWS($B$31:B31)>SUM(((LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)="H")+(LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)="O")+(LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)=""))*(INDEX($B$8:$E$12,0,MATCH($B$30,$B$1:$E$1,0))<>"N/A")),"",INDEX($A$2:$A$6,SMALL(IF(((LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)="H")+(LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)="O")+(LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)=""))*(INDEX($B$8:$E$12,0,MATCH($B$30,$B$1:$E$1,0))<>"N/A"),ROW($A$2:$A$6)-ROW($A$2)+1),ROWS($B$31:B31))))
Book1 | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | 1/1/2019 | 1/2/2019 | 1/3/2019 | 1/4/2019 | |||
2 | STAFF 1 | D-o | O-d | H-r | R | ||
3 | STAFF 2 | O-d | H-r | R | W | ||
4 | STAFF 3 | H-r | R | E | |||
5 | STAFF 4 | R | D | D | |||
6 | STAFF 5 | D | H-r | ||||
7 | |||||||
8 | STAFF 1 | swap | swap | ||||
9 | STAFF 2 | N/A | |||||
10 | STAFF 3 | N/A | |||||
11 | STAFF 4 | ||||||
12 | STAFF 5 | text | |||||
13 | |||||||
28 | |||||||
29 | |||||||
30 | 1/2/2019 | ||||||
31 | STAFF 1 | ||||||
32 | STAFF 2 | ||||||
33 | STAFF 5 | ||||||
34 | |||||||
35 | |||||||
36 | |||||||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B31 | {=IF(ROWS($B$31:B31)>SUM(((LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)="H")+(LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)="O")+(LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)=""))*(INDEX($B$8:$E$12,0,MATCH($B$30,$B$1:$E$1,0))<>"N/A")),"",INDEX($A$2:$A$6,SMALL(IF(((LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)="H")+(LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)="O")+(LEFT(INDEX($B$2:$E$6,0,MATCH($B$30,$B$1:$E$1,0)),1)=""))*(INDEX($B$8:$E$12,0,MATCH($B$30,$B$1:$E$1,0))<>"N/A"),ROW($A$2:$A$6)-ROW($A$2)+1),ROWS($B$31:B31))))} | |
Press CTRL+SHIFT+ENTER to enter array formulas. |