Excel Staff Rota

[Simont485]

I have a staff rota and want to input a date to show staff who are available to be called for cover.

I have tried Index Match and Vlookup but my Excel experience falls short.

I have an example spreadsheet but cant see how t upload?

 

[Scott T]

You cannot upload files to this site. you can upload the file to Dropbox or similar site and post a link here. You can also post a sample of your file like

	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

<TBODY>
</TBODY>

 

[Simont485]

	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

<tbody>
</tbody>

In the example
Both sets of staff are the same persons name. I will just explain Column B+C.

In a cell, lets say B30, I want to input a date from the rota above. Below that date I would like to see a list of the staff available for shift cover.

So for date 01/01/2019 id expect the list to show Staff 5.....because Staff 5 has blank cells for that date

For Date 02/01/2019 Id expect to see Staff 1(O and Blank) Staff 2(H and Blank) and Staff 5(both Blank).



Criteria

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

<tbody>
</tbody>

I hope that helps.....the real rota is 365 days by about 50 staff......its a bit of a headache!

 

[Scott T]

Try

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.

 

[Simont485]

ERM...... thank you I am speechless!

That is truly amazing! Woks perfectly straight out of the tin!

Cannot thank you enough! I was confused just trying to explain it in English lol!!

Again Thank You!!

 

[Simont485]

In cells B8:E12 where NA is used I need to ignore all other text.

So if a cell in that rage contained 'swap' or 'name' it would be treated as a blank.....

 

[Scott T]

As before commit with CTL+SHIFT+ENTER

=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))))

 

[Simont485]

Ok...so made the change to <>"NA" as shown but it now ignores all text......I just need to ignore everything but NA......

 

[Scott T]

Is this what you expect? For me I can put text into the cell and it still shows unless I enter N/A.

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.

 

[Simont485]

Yes my mistake....spotted the <>N/A easy enough but missed the 'Left(Index......'had lost the left!!

Works perfectly!