+ Reply to Thread
Results 1 to 3 of 3

Excel as an Outing Bookings program

  1. #1
    Registered User
    Join Date
    03-26-2020
    Location
    Australia
    MS-Off Ver
    2016
    Posts
    1

    Post Excel as an Outing Bookings program

    Hi there,

    I have been doing a little bit of research into how best to manage my outing bookings. I work in an administrative role managing daily outings for senior citizens. Basically I am needing a little bit of guidance on where I should start looking for best formatting ideas to manage the bookings in a way that is easy, easy to look at, and possibly something which can do reporting like outing occurences for a particular name, number of attendees for a particular venue, charts that will show attendance rise and fall week to week, month to month, year to year etc. I am thinking if I have the foundations right for the data the reporting side of stuff would just follow.

    I have attached a copy of the worksheets I am currently working with, and will give a bit of a summary of how it works. What I need is some direction on where I should be looking in terms of formatting. Should I be using a different program than excel? Is there a way to make my formatting less prone to errors or changes etc.


    So first step is that a client will call to make a booking.
    I record their name in the booking w/sheet (Example 1), select the outing from a drop down list which pulls from the data worksheet in Example 1, record the booked date and make the status as active. I then have a pivot table set up in the worksheet which is filtered to show only active bookings for each outing date. This has been working really well except for one problem. Each outing booking has a certain amount of spaces before a client would need to be placed on a waiting list. So basically I have to enter each booking individually, refresh the pivot table, check the number of attendees before I can tell if they would be on a waiting list or not. And I don't know how best for the pivot table to show clients who are waitlisted on an outing as opposed to the active clients. I am happy with this system atm except for this part. If I could work my pivot table or the booking dropdown list to flag when a certain number has been reached, and have the pivot tabel show in a presentable way which clients are on the waiting list, I would be happy to continue working with this system.

    Example 2 is what is known as the pick up list. Basically it is the clients from the pivot table copied over to the w/sheet titled 1 with their data prefilling from the data w/sheet of Example 2. I then paste the values into a blank worksheet so I can manually order them into a pick up order based on their address. Then this get copied over to w/sheet titled 2 in the pick up tabels (rows 23-32 and 41-50) When the date is recorded in the cover sheet of w/sheet 2 I have it formulated to prefill in the pick up tables and the finance sheet down the bottom. Previously the cvoer sheet was one w/sheet, then the pick up lists, and then the finance sheet. I had multiple excel docs open and spent a lot of time hopping between them so I put the cover sheet, pick up list and finance sheet together so all outing dates for the month are on a worksheet on the same s/sheet.

    The cover sheet pulls the phone numbers from the volunteer information worksheet once the staff/volunteer name is entered
    The venue field in the cover sheet is a drop down list of data from the venues worksheet
    The cost codes in the finance sheet then pre fill based on the formula pulling from the costings worksheet.

    One of the issues with the finance sheet is that it is now in landscape format because it is dependant on the column widths of the cover and pick up sheet above it.

    Do you have any advice for me on this?

    I am only very amateur with excel so I am not 100% sure if I am using the right formulas and formatting etc.

    What I am trying to achieve is to make the data entry side of the bookings and subsequent pick up lists as automated as possible, so that the only data I really need to manipulate will be the order of the clients on the list based on the pick up order on the bus.
    The biggest issues I am currently having with the whole process is this;

    • I have to keep changing row heights and column widths so that the data is presentable for printing.
    • I would like to by-pass the process where I have to move the original booking data three times before it reaches it’s final destination on the pick-up list. However I still want to maintain the booking data so that it can show a quick view of the month with outing dates and client names and total number of clients on outing (like the pivot table)
    • When I ask the costs code to prefill based on the costing data –If we have two outings on the same day, I have to have the outing date as dd/mm/yyyy 01 and dd/mm/yyyy 02 so that the cost data is correct. Not sure if there is a way around it.
    • Sometimes where the data exceeds the cell capacity I would like it to automatically show all the data and not have it unseen when it is printed out.
    • Everytime you delete a row (i.e a client is deleted from the pick up list) the rest of the data and forms are impacted and have to be reworked for printing margins
    • If I delete something from the booking sheet or the pick up list I would like it to auto remove from the finance sheet.
    • I don’t want anyone else to be able to change any of the data unless it can be tracked, otherwise there is no way to know how or where something is being changed

    If anyone has any advice on what the easiest way to manage and present this data would be, that would be great. There is so much information online and I am happy to access these resources to find a better way of doing my work, but I am just really hoping for some direction on what the most accurate, efficient and error proof way of doing this might be so I know where to start looking. Previously this data was managed using word and I have used my very limited excel knowledge to change it up a bit, but I am concerned that in all my time saving endeavours I may be doing way more legwork than what I actually need to do.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    9,092

    Re: Excel as an Outing Bookings program

    Hello Excel Enquirer and Welcome to Excel Forum.
    There are a lot of requests packed into one post.
    I suggest breaking the task down into smaller parts and once one part is resolved then mark that thread as 'Solved' and start a new thread to work on the next part.
    For example there is the issue of the wait list. One consideration might be to add a column to Table 3 (I) which counts the number of persons signed up for an active event using: =COUNTIFS(D$2:D2,D2,G$2:G2,"")
    The Active/Cancelled column could be populated using: =IF(ISBLANK([@[Booked date]]),"",IF(ISNUMBER([@[Cancelled date]]),"Cancelled",IF([@Count]>8,"Wait List","Active")))
    Note that 8 is an arbitrary number which may be changed.
    Now when the pivot table is refreshed it will only show the names of the people whose status is active.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    02-29-2020
    Location
    Breda, NL
    MS-Off Ver
    2016
    Posts
    201

    Re: Excel as an Outing Bookings program

    Welcome to the Forum,
    you could automate the updating of the pivot table with a socalled event, i.e. placing a macro that does the job for you.
    Like in the example that I uploaded.
    Furthermore it would be handy if you add in the title the available places, like I did in the example, so that you immediatly see if your condition is met.

    BTW you have to remove the accent " ' " in front of the formula and copy this formula down.
    If I would do that, you will get errors, because my Excel-language is Dutch.

    Cheers
    Erwin
    Attached Files Attached Files
    Last edited by Eastw00d; Today at 11:30 AM.
    How to learn VBA: RECORD A MACRO ... and study it!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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