Excel Staff Rota

S

Simont485

Board Regular
Joined
May 19, 2018
Messages
50
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?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
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

[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Header1
[/TD]
[TD]header2
[/TD]
[TD]header3
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]data
[/TD]
[TD]data
[/TD]
[TD]data
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]data
[/TD]
[TD]data
[/TD]
[TD]data
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]data
[/TD]
[TD]data
[/TD]
[TD]data
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]data
[/TD]
[TD]data
[/TD]
[TD]data
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]data
[/TD]
[TD]data
[/TD]
[TD]data
[/TD]
[/TR]
</TBODY>[/TABLE]
 
Last edited:
Upvote 0
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD]1/01/2019
[/TD]
[TD]02/01/2019
[/TD]
[TD]03/01/2019
[/TD]
[TD]04/01/2019
[/TD]
[TD]ETC.....
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]STAFF 1
[/TD]
[TD]D-o
[/TD]
[TD]O-d
[/TD]
[TD]H-r
[/TD]
[TD]R
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]STAFF 2
[/TD]
[TD]O-d
[/TD]
[TD]H-r
[/TD]
[TD]R
[/TD]
[TD]W
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]STAFF 3
[/TD]
[TD]H-r
[/TD]
[TD]R
[/TD]
[TD][/TD]
[TD]E
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]STAFF 4
[/TD]
[TD]R
[/TD]
[TD]D
[/TD]
[TD][/TD]
[TD]D
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]STAFF 5
[/TD]
[TD][/TD]
[TD][/TD]
[TD]D
[/TD]
[TD]H-r
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]STAFF 1
[/TD]
[TD]swap
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]STAFF 2
[/TD]
[TD]N/A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]STAFF 3
[/TD]
[TD]N/A
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]STAFF 4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]STAFF 5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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
[TABLE="width: 536"]
<tbody>[TR]
[TD="width: 75, bgcolor: transparent, align: right"]1
[/TD]
[TD="width: 576, bgcolor: transparent, colspan: 9"]In cell B30 I want to input a date.
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent, colspan: 9"]Under that I want to see a staff list based on:
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent, colspan: 9"]Blank Cells and those containing H or O(left most letter) from the top table B2:E6
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, colspan: 10"] combined with cells NOT containing NA in the lower tableB8:E12
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, colspan: 9"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Info
[/TD]
[TD="bgcolor: transparent, colspan: 6"]W,D,R and E are shifts fulfilled be those staff. H is holiday
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 10"]It would need to apply to all cells in both tables.
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 10"]List would expand/contract as rota and/or date changes and be blank when no date is present
[/TD]
[/TR]
</tbody>[/TABLE]


I hope that helps.....the real rota is 365 days by about 50 staff......its a bit of a headache!
 
Last edited:
Upvote 0
Try


Book1
ABCDE
11/1/20191/2/20191/3/20191/4/2019
2STAFF 1D-oO-dH-rR
3STAFF 2O-dH-rRW
4STAFF 3H-rRE
5STAFF 4RDD
6STAFF 5DH-r
7
8STAFF 1swap
9STAFF 2N/A
10STAFF 3N/A
11STAFF 4
12STAFF 5
13
14
27
28
29
301/1/2019
31STAFF 5
32 
33 
34 
35 
36 
37    
Sheet1
Cell Formulas
RangeFormula
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.
 
Upvote 0
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!!
 
Upvote 0
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.....
 
Upvote 0
As before commit with CTL+SHIFT+ENTER
Code: 
=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))))
 
Upvote 0
Ok...so made the change to <>"NA" as shown but it now ignores all text......I just need to ignore everything but NA......
 
Upvote 0
Is this what you expect? For me I can put text into the cell and it still shows unless I enter N/A.


Book1
ABCDE
11/1/20191/2/20191/3/20191/4/2019
2STAFF 1D-oO-dH-rR
3STAFF 2O-dH-rRW
4STAFF 3H-rRE
5STAFF 4RDD
6STAFF 5DH-r
7
8STAFF 1swapswap
9STAFF 2N/A
10STAFF 3N/A
11STAFF 4
12STAFF 5text
13
28
29
301/2/2019
31STAFF 1
32STAFF 2
33STAFF 5
34
35
36
Sheet2
Cell Formulas
RangeFormula
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.
 
Upvote 0
Yes my mistake....spotted the <>N/A easy enough but missed the 'Left(Index......'had lost the left!!

Works perfectly!
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Schedule based on priority and availability
Replies
0
Views
271
Gwill1983
G
M
  • Question Question
How to track choices in a workbook from several drop down lists and reflect the running choices throughout the month
Replies
1
Views
437
bobaol
B
R
  • Question Question
Staff Rota Formula
2
Replies
11
Views
738
Fluff
Fluff
T
  • Question Question
Excel - Std 2021 running very slow
Replies
0
Views
55
thisisrahul
T
N
  • Solved
V-Lookup Issues
Replies
4
Views
177
dreid1011
dreid1011

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top