Creating a Master Sheet for Shift Rotas

New Contributor

Hello everyone,


I am no expert when it comes to excel, I have created a spreadsheet with worksheets for each location I have staff working in. In each sheet's column A has all the staff names and Row 1 has dates going forward. For each location staff shifts are plotted for example: staff member 3, 8-6 on 12.08.22. 


In my master sheet I have the same list of staff names and dates in their respective locations. The result I am looking for in my master sheet is for each cell to check the same cell location across all worksheets for contents i.e "8-6". When it comes across a cell that has content, it copy's that back to the master sheet. If there is no content then the master sheet cell is left blank.


is this possible?


Thank you in advance


3 Replies

@Joe_Gates_BHL Why not put all information in one table and add a column for the location? That way, you can analyse data however you want it.

Hi @Riny_van_Eekelen thanks for replying  


How would that work if I have dates across the top (I need to be able to see which staff are working on specific dates) and staff names down the side. Not sure how I would split this per location in one table?




Perhaps a setup like this:S1667.png