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.

Thanks so much for this, I will take a look now. Greatly appreciated