As to "a total of individuals that are working that day in that particular shop" that may be accomplished using: =COUNTIFS(C$4:C$45,$B48)
B48:B56 on sheet 2 are populated with the 9 shops.
Row 57 shows an overall total
As to "and then to be displayed something similar to Sheet 3" may be accomplished using:
Formula:
=IF($A6<>"","",IFERROR(INDEX('Sheet 2'!$A$4:$A$45,AGGREGATE(15,6,(ROW($A$4:$A$45)-ROW($A$3))/('Sheet 2'!C$4:C$45=$B6),COUNTIFS($B$5:$B6,$B6)-1)),""))
Note that previously blank cells in column B are filled with the shops and the font color is set to white.
As to setting up the schedule given the location conditions, it may be that someone can do that, however I feel that it would be more efficient to manually set up a rotation (one time through) and let Excel continue the rotation into the future.
I did add data validation drop downs to cells D4:P45 which may make it easier to enter the rotation as opposed to typing.
Let us know if you have any questions.
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks