Staff rota to automatically generate separate daily planners

[lukela85]

Hi all,

I'm not really sure where to start with this one so here is what I would like:

I have a rota with approximately 70 staff members on. Ideally, what I would like is for a 'daily planner' for each separate day of the week to be generated automatically.

So if sheet 1 lists all the staff members names and working hours for Sunday to Saturday, then sheet 2 would automatically list all those working Monday, sheet 3 listing all those working Tuesday etc.

Hope this is enough detail to start with.

Thanks,

Luke

[shukla.ankur281190]

Hi

Can you upload excel workbook with expected result and explanation.

Regards
Ankur

[lukela85]

I've literally just knocked up a quick template of roughly how I would like it to look.

Thanks for your help,

Luke

[shukla.ankur281190]

Try

Put in sheet Sunday
A1=MID(CELL("filename",A1),SEARCH("]",CELL("filename",A1))+1, SEARCH("]",CELL("filename",A1))-SEARCH("[",CELL("filename",A1))-1) this will give you exact sheet name.

Then
A4=IFERROR(INDEX('Summaary '!$A$5:$A$9,SMALL(IF(OFFSET('Summaary '!$A$3,2,MATCH(Monday!$A$1,'Summaary '!$B$3:$O$3,0),5)<>"",ROW('Summaary '!$A$5:$A$9)-ROW('Summaary '!$A$5)+1),ROWS($A$1:A1))),"") with CTRL+SHIFT+ENTER and drag down, this will give you name list of that day.

now

B4=IFERROR(INDEX('Summaary '!$A$3:$O$9,MATCH(Monday!$A4,'Summaary '!$A$3:$A$9,0),MATCH(Monday!$A$1&Monday!B$3,INDEX('Summaary '!$A$3:$O$3&'Summaary '!$A$4:$O$4,),0)),"") and drag towards the cell and down.

Now you can copy paste this sheet in next new sheet and you will just neeed to change only sheet name.

Check the attached file.

[lukela85]

Thanks, that works really well.

Is there any way that where I have a staff member on holiday (ie I need to put it on the master rota), it isn't included in the daily planner?

Luke

[shukla.ankur281190]

Can you upload workbook with expected result with explanation.