Help developing (or finding) room scheduling document for 6 libraries with multiple rooms

This is a prototype with the basic functions, I want to know a bit more about the lab bookings, and the cost of bookings etc. What kind of information do you want to track and could you include a sample workbook?

The code in the workbook is a bit fragile in that you should not add rows or columns to the Calendar sheet. About the only thing you could do is hide column A. The Calendar Sheet shows a rolling 45 days schedule starting with the current date. It has three buttons: Add, Delete, Refresh.

To add an event, highlight a time range in a single row and click the add button. This will bring up a dialog box that asks you for the name of the event. It then creates a record on the data sheet and refreshes the schedule.

To delete an event, highlight the event and click the delete button. This action deletes the record from the data table and refreshes the schedule. I could add a "safety net" and ask you to confirm the delete if you want.

The refresh button simply refreshes the calendar. I have it arranged that the calendar refreshes every time the workbook is open.

When you add a booking, I can bring up a user form instead of the dialog box to fill in the additional information you want. I could probably integrate this into the existing data table.

Some things to consider would be booking beyond 45 days (but this would require the development of code to detect conflicts - by visually selecting the blocks this is avoided) and some sort of a generator for recurring bookings - again, a module would have to be developed to avoid conflicts.

One thing I will provide after I get the additional information is another button for details. Select a booking, click on the button and a window with the details of the booking will open up.

I notice a couple of things on the sample that I was not able to see on the picture, namely the class duration under the name of the class.

I also see that you have a preference for booking by month as opposed to a rolling calendar. I think I can arrange both. I can give you a "template" sheet (that can be hidden) and a button that says "Make Sheet" and it will prompt you for the date, and create the sheet. Do your scheduling on the monthly sheet, and since the Rolling 45-day calendar already reads the data, it can give you an "at a glance" view of what is coming up.

You also mentioned multiple rooms. I now see how you are handling that. I would expect that each library would have different rooms or does this spreadsheet cover all rooms in all branches?

Here is how I would imagine that this will work, see if you agree. It is now July and July is booked and being booked. You open up a sheet for August and apply the in-house classes to it (we will work on how to set up that data) then you open up August for public booking. If you are really ambitious you could do the same with September.

We'll probably need some auxiliary tables with room names and what columns they occupy on the spreadsheet and their costs. Another table would contain class names and their schedule which I hope is something like every Thursday from 10-11. I'm not sure I can work out things like first Wednesday of the month. At the worst, you may have to fill out these manually rather than have them recurring.

Then I noticed that you have "Conditional formatting" for External bookings, and other events - Your events will be managed by filling in a database with the date, start and stop times (I'll figure a way to merge these with public bookings) so we will need some sort of key to indicate the color you want on these.

Public bookings will be handled as in the sample: highlight, click add or delete and I will assign a default color for those.

It is all do-able, but it is a rather big project. Since I will be working it in my spare time, it will take weeks and maybe a couple of months.

Developing a list of what's coming up for what room for a time period should not be an issue. It sounds like work for a pivot table.

I think I have enough information to get started. I will work with this example, but you will have to adapt it for the other locations. I will design it so that the code will be "data driven" and while you may have to change some information in some tables, you should not have to get "under the hood (or bonnet)" to mess with the code.

I was fortunate in that a lot of my code was reusable.

There is still some cleanup and I still have to give you a function where you can fill out the classes in a table and have them added to the data and merge that with the ad-hoc scheduling. Also, I may have to add “Conditional Formatting” to apply color to the type event.

This version has the ad-hoc scheduling but applied to a monthly sheet and extended to the various rooms.

The first “stop” is the template sheet. I recommend that the other branches use this sheet, but change the names of the rooms to meet their needs. Once again, I caution about adding rows. Columns are not a concern as they will be addressed on the parameters sheet.

Once the other branches set up their rooms, this task is done. It’s a “one and done” type of task.

I set up a table on the parameters sheet. You fill in the room, the capacity, the fee and the start (9:00) and end column (21:30) for the room. The table will calculate the rest. Again, this is a “one and done” except for the fees. I don’t have any immediate use for the fee column, but you might want to use it for reporting in the future.

Next is the Control Panel. The only function I have there is to create a new sheet. Cell B2 has a drop down list for the months and Cell B3 only accepts a whole number equal to the current year or next year. Cell B3 is calculated by what you enter in the other two cells. There is no need to touch it.

Click on the make sheet button. This action checks to make sure that the sheet doesn’t already exist and makes a copy of the template and populates it with the dates. It deletes unused date rows (like February 30 and 31 and 29 in non-leap years).

It then fills the new calendar in with the data from the database.

You can add and delete ad-hoc records based as in the previous versions by highlighting the range and clicking on the add or delete buttons.

The data sheet has been expanded to include the Room Name. The pivot sheet shows a bit of what can be done with this table. You can look up on what’s going on by week or the schedule for a particular room.

I think I am now ready to see a sample of the inhouse class data. I think, because of the way things fell into place that integrating this will be relatively easy.

Please feel free to expound upon the process you use to schedule these classes.

Play with this spreadsheet and provided feedback.

So you don't have clairvoyance-net installed yet? *Sigh* I guess I'll have to do it the old-fashioned way and actually attach the file .

I did not notice that the file had grown too big for its britches and had to be compressed to be loaded.