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.
|