Populate automatic ROTA by date

Originally Posted by BlindAlley

See if this is something you could work with and we can go from there to set up what you need to count & record. You could keep the entire year intact or chop it to make a four week schedule.

The Holiday section (to the right of the actual schedule columns) would need to be changed to the Holiday dates for Deutschland, it has been set for Canadian Holiday dates at the moment.

Let me know/

Hi mate, this is truly breathtaking! I was speechless for a moment.

It does overlook a key factor I need though, the shift codes to allocate shift. The tracker is for let's say HR personal. The Rota is for staff to read only....

One other factor I forgot to mention and include in the first post was the "Login Data" tab. Staff would log into the system which records this, it is then available to download as a report. This dumped into the "Login Data".

In theory, I would like the Tracker to auto fill if someone was IN if they were logged in, otherwise blank (this could be filled in manually)... Or is that more vba?

Many thanks BA!

If you want to populate the Rota from the Tracker in combination with the ShiftCodes then you need the extend your formula in the Rota to something like this (cell E8):

Formula:

=IF(INDEX(Tracker!$E$8:$J$11,MATCH(Rota!$D8,Tracker!$D$8:$D$11,0),MATCH(Rota!E$7,Tracker!$E$7:$J$7,0))<>"in",

                   INDEX(Tracker!$E$8:$J$11,MATCH(Rota!$D8,Tracker!$D$8:$D$11,0),MATCH(Rota!E$7,Tracker!$E$7:$J$7,0)),

                   INDEX('Shift Code'!$H$2:$M$16,MATCH(Rota!$D8,'Shift Code'!$A$2:$A$16,0),MATCH(Rota!E$7,'Shift Code'!$H$1:$M$1,0)))

Horrible formula, but you can replace parts of it by meaningfull names for readabilty.