Hi all,

I have made a rota system for a nursery that was being done by hand ( I am taking over and can't do it like that, takes too long).

I was trying to enable to system to look at a fortnightly rota and want it to look at the table and put a person in a box if it is the correct day or not.

I have used an if statement to add those that are the same every day but swap depending on if it is week 1 or 2, but other staff's hours are different or might not be in depending on the day.

I was trying to use an if statement to say if week 1/2 then look at the appropriate table and then looking at the week day, select the person for that day and put it in the box?

I was thinking if that is not possible I could easily split and have a seperate file for week 1 and 2 and just focus on getting the page to look at mon-fri and position them in if it is the correct day. That is what I was going to try next but just wanted some support on which route is probably going to be more viable.

page 1 of the file has the table I am presenting the rota on, while page 2 has the tables for staff times and work days, sorted into their appropriate rooms.


thanks all

Attached Files

Copy of Rota file test.xlsx (20.7 KB, 10 views)

I'm not clear on how this works, but I applaud your effort at automation. So let me see if I can figure this out.

On sheet 2, I see what appears to be two schedules, both are Week 2. Is that right or should one be Week 1?

On sheet 1, there are tables with "buckets" of half hour time slots. The headings of Sunshine, Starlight, and Rainbow. Are these the three rooms? If so, is it your intention then to populate these buckets with personnel based on the schedules on sheet 2?

Sorry that should have said week 1

and Yes that is correct I was looking to fill in with those on different days in the correct times, leaving out the full time people but Those who do less hours but are not always in every day depending on which week it is

I think a total tear down is in order. Among the challenges I see with the current sheet and how you want to automate is that in sheet1 you have time buckets in rows and rooms in columns. In sheet2, you have rooms in rows, days in columns and shift intervals as intersecting values. I'm afraid the two are too dissimilar to relate them easily and I doubt any solution would be reliable.

Perhaps if you give me an idea of what the objective is, maybe we can come up with a better way. What is the purpose for the rota? Is it to show each employee their work schedule? Is it to post in each room to show which employees should be there?

The way I see it, a basic data table for such a schedule should have the following: Week (1 or 2), Day (Mon, Tues, ...), Room (Sunshine, ...), Name (Bill, Sara, ...), Start and End. I have attached such a table with some dummy data for you to look at. Look at the Lists tab. These are the lists that the dropdowns on the Schedule tab use. Also, times are in 24-hour format. This of course can be changed to suit you.

This is not a final solution. Merely a jumping off point.

Attached Files

Rota new.xlsx (11.5 KB, 12 views)

Particularly the drop down bars for searching, can I use the drop down bars so if I select it as week 1 it will show all staff working week 1,

and then if I select monday for it to only show those in on week 1 monday?

is that what you were looking at.

If so that would be great way thanks

Look at what I attached. Even that basic table can be filtered by any field you like - week, day, room, employee, etc. - and in myriad ways.