Populate automatic ROTA by date

[manny88]

Hi guys,

I'm trying to create a rota where it will automatically create and display the shifts of staff for the current week (Mon to Fri) and the following four weeks by entering the Week Commencing date (eg. 28/11/2016 will show this week, and the following 4 weeks. 05/12/2016 will show that week and the following 4 weeks ahead). This should populate from the shift codes manually entered in the Tracker tab for each staff.

The tracker is used to record instances of staff during the week. It also keeps count of headcount in the office and holiday allocations, and count of holidays.

How would I go about creating this? I have attached what I am working with with some helpful comments. Hope someone can get back to me.

any question, feel free to ask. Many thanks in advance, much appreciated.

[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/

[Tsjallie]

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):

Please Login or Register  to view this content.

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

[manny88]

When I said populate I meant the rota self populate the current week's shifts along with the upcoming 4 weeks too...

Is this something that would require vba instead?

[manny88]

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!