How to make a Booking Planning-report

**excel90** · 01-26-2010, 06:21 PM

Hello forum team

I want to make a booking/reservations report based on excel and I want it the following way;

On my excel book I have a "bookingList" and "Planning". on the BookingList are listed all the bookings received. After inserting this data, I want a planning which could display all bookings by apartment.

I want to fill "planning" sheet automatically based on the bookinglist sheet, this to be showned, for exemple, all bookings listed, on "bookingList" Sheet, for apartment C1A, to be showned on "planning" by apartment and for the dates occupied in the same row. i.e., fill "planning" sheet based on all bookings in "BookingList" Sheet.

Hope this is sufficient info.

Many thanks for all help that can be provided

**6StringJazzer** · 02-10-2010, 11:08 AM

I have attached a solution to this that uses VBA. Hopefully it's not too late

I am having trouble with your sample data. You show bookings for C1E that don't appear on your Planning sheet.

Also, for example, for C1A, you show 12 x's, but your sample BookingList only counts 11 nights.

I have assumed that an "x" means that apartment is booked for that night. Once an apartment shows an "x", the next blank day is the day of departure. Therefore, 4 x's in a row means 4 nights booked.

Also, I can't tell how you want the data sorted. It is not sorted by apartment, arrival date, departure date, nor number of nights.

I have sorted by apartment, then by arrival date.

Are apartments required to have a free day between bookings? If you have one booking start the day after another ends, there is no way to tell that it is two separate bookings.

Also, you have a finite-space problem. Eventually you will reach 256 columns, less than a year, and have to start a new sheet. You may have issues at the starting end figuring out which bookings are new vs. which simply continue from the prior time period.

Last, based on my knowledge of how rooms are booked, you may be better off doing this inside out. That is, use one sheet to record the arrival and departure date for a booking, and then post those dates to the other sheet. The data entry is much easier. You would have to add logic to determine that all nights requested are available.

This should get you started.

**6StringJazzer** · 02-10-2010, 11:11 AM

Originally Posted by 6StringJazzer

Last, based on my knowledge of how rooms are booked, you may be better off doing this inside out. That is, use one sheet to record the arrival and departure date for a booking, and then post those dates to the other sheet. The data entry is much easier. You would have to add logic to determine that all nights requested are available.

My apologies, I just realized this is exactly what you asked for, so my solution is inside-out. Give me a moment to fix!

However, the things I identified as problems still exist.

**6StringJazzer** · 02-10-2010, 12:24 PM

Attached is a solution. This assumes that all apartments are already listed on the Planning sheet. The Nights column becomes irrelevant. You could make it a formula to calculate it from dates, or you could make the Departure Date a formula to calculate from arrival date and number of nights.

You'll see I added a column for error messages. When you automate this there are several error conditions you must consider. I added examples to show when these conditions occur.

**excel90** · 02-11-2010, 06:58 PM

Thank you very much for your great help! This is exactly what I need. Much appreciated, 6StringJazzer!
It must have given you alot of work. Sorry to trouble you again but, as I need to add a column in bookingList for "name" and "Tour Operator", do you think you could help on this too?

Again, hank youvery much!!