+ Reply to Thread
Results 1 to 10 of 10

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

  1. #1
    Registered User
    Join Date
    07-16-2018
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    5

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

    Hi everyone,

    I've been casually using excelforum for a while now with basic queries but I dont think this one has been answered (I've searched around for a while!). Thank you for your patience - this might be me asking for too much, but hopefully, you'd be able to let me know if it is...

    I work for a library service which has 6 libraries and each one has a handful of rooms available to hire. We also run internal classes in these rooms. Currently, we use a set of VERY basic spreadsheets (one per branch) I splashed together, to book in classes and external hirers. Can anyone suggest a better spreadsheet that could be more user friendly/clearer/less prone to mistakes? Currently, we're just selecting cells and merging them between the hours of a room booking (please see attachment).

    It would be great if we could simply ahve a dropdown option for start time and end time and a way to indicate other details such as cost of booking, details of the hirer etc.

    To complicate things further... We also make a weekly lab display that ties into this spreadsheet. So once the bookings are taken, staff then create a display for the public to know what bookings are taking place in each room. It would be amazing if this could be fed in from the lab booking spreadsheet.

    Does any or all of this sound plausible? My excel knowledge is incredibly basic, but I have lots of time (as this is kind of a pet project) so I'm willing to learn or follow a step by step guide.

    Thank you SO MUCH for giving your time to read this and for any suggestions!

    -Uzbuzz
    Attached Images Attached Images

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,123

    Re: Help developing (or finding) room scheduling document for 6 libraries with multiple ro

    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.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    07-16-2018
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    5

    Re: Help developing (or finding) room scheduling document for 6 libraries with multiple ro

    Oh wow, thank you so much for that dflak! I really appreciate it! I've attached a copy of our scheduler - it's just a sample so it's not fully filled in but the May tab has some examples of what our bookings look like. we currently use the comment function in Excel to add information such as who has booked the room, their contact details, when they booked it etc.

    As for the information you requested about our bookings: Firstly, we book in a HUGE amount of in-house classes; the majority of our calendar is taken up by courses that run throughout the year. Then, work on a first come first served basis for the public. All the rooms are available to hire at different prices (they're listed on top of each room in the attachment). But they range from £27.50 per hour to around £100 an hour, and we offer a half price discount for registered charities. People generally book via email or over the phone.

    A key element of the booking schedule has been user friendliness - not everyone in the team who would be responsible for inputting data is great with technology, so ease of use is paramount. That's why the one I put together is all colourful, big and bright. It was a really fine line between getting all the information we need to capture and also making it usable by staff - I know they need to be trained... but that's sometimes easier said than done!!

    Honestly, I really appreciate what you've put together so far, it's so impressive! I love the second tab which has all the information from the first, because currently, we fill in the lab schedule and at the end of the week, open up publisher and input the following week's bookings manually to be displayed on the doors of each lab - if we can develop a way to automate that, it would basically save half a day's worth of staff time, which would be incredible!!

    (forgot to add attachment in first post!!! Sorry!)
    Attached Files Attached Files

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,123

    Re: Help developing (or finding) room scheduling document for 6 libraries with multiple ro

    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.

  5. #5
    Registered User
    Join Date
    07-16-2018
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    5

    Re: Help developing (or finding) room scheduling document for 6 libraries with multiple ro

    dflak, thank you! Sorry for the late reply - I fell asleep!

    I feel like I should give you a few more details of our processes. In the summertime (around July tim) we get the following years courses given to us by curriculum managers. This is information on courses that will take place between September and the following July. So as a team, we sit and plot all the courses in all the libraries. So the ability to go forward for about a year is important, because staff then look at this calendar for scheduling new classes/meetings etc.

    As for the different rooms; each library has it's own set of rooms and its own spreadsheet. We did this because there are 6 members of staff working on these spreadsheets and having just one means that multiple people can't work on it at the same time.

    Class times vary like mad, but yes, they remain the same during a term- say Monday 9.15am to 11.15am for 8,12, 24 or 35 weeks. Occasionally individual classes get cancelled (for instance if a tutor is calling in sick) which need to be removed from the spreadsheet and replaced later in the term.

    Can I just say - I LOVE the data tab, which potentially cuts out a HUGE amount of work the team is doing now. THANK YOU! If there's ANY other info you need, please do let me know and if there's any work I can do towards it, please let me know!!!

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,123

    Re: Help developing (or finding) room scheduling document for 6 libraries with multiple ro

    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.

  7. #7
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,123

    Re: Help developing (or finding) room scheduling document for 6 libraries with multiple ro

    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.

  8. #8
    Registered User
    Join Date
    07-16-2018
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    5
    Hi dflak.

    I just wanted to let you know I just came across your post and will be back in the office first thing tomorrow to give it a try. I just want to take this opportunity (on my mobile, so sorry, not able to download your attachment!!) To say you're a real star and I really appreciate you giving up your time on this!

    Like I said, I'll be in the office tomorrow and play around and provide feedback.

    Thanks again!!!

  9. #9
    Registered User
    Join Date
    07-16-2018
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    5

    Re: Help developing (or finding) room scheduling document for 6 libraries with multiple ro

    hey dflak,
    I don't think you attached the spreadsheet.
    Really excited about trying it out!

    Cheers

  10. #10
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,123

    Re: Help developing (or finding) room scheduling document for 6 libraries with multiple ro

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] If date matches and room matches, return room info in to cell
    By HonorBray in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-12-2018, 12:09 PM
  2. [SOLVED] How to Calculate Hotel room nights booked, for each night, and broken down by room type?
    By salsadantzr in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-09-2018, 04:53 PM
  3. Room Allotment:remove allotted room from dropdown list
    By amdrosm in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 05-05-2017, 04:50 AM
  4. Converting tabular data into room-by-room
    By tomca in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-03-2016, 07:00 AM
  5. Copy multiple workbooks located in various SharePoint libraries to a mastser document
    By walterb02 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-10-2014, 01:40 PM
  6. Scheduling: Calendar style scheduling based on scheduling list
    By denkatprieto in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-19-2012, 11:10 AM
  7. Developing macro event to allow multiple choices on drop down menu
    By Brendan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-06-2006, 11:35 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1