Hello all,
I run a small hotel and, with the aid of the superb people on this site, have created a solution to track my clients and their reservations.
What I am now trying to do is to use this information to create a type of Gantt chart to show the reservations as a chart by room number and date which shows the clients name.
In the attached file I have manually entered the reservations on the planning tab as an example.
As usual, any help you can offer is greatly appreciated.
Last edited by AliGW; 10-08-2023 at 04:59 AM. Reason: Thread title improved - generic thread titles are not of much use.
Just to say - the cell merging will NOT be possible with a formula. To get EXACTLY what you want would require VBA. However, the shading can be achieved with a formula. Which way do you want to go with this?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Hello,
I have checked out VBA and that looks another level way beyond my skill set. The original idea was it all to be automated to reduce manual input. If to do this via VBA is too difficult and I have to manually enter the reservation into the planning chart, then I might as well apply the shading manually as well.
I should add that I am going to be adding macros into my worksheet, but it is the coding of the macros in VBA that is beyond me. I understand the basic concept and recording of a macro...
It's not going to be difficult for someone with coding skills, probably. The only problem with VBA if you are not familiar with it is when something goes wrong or needs tweaking, you'd probably have to come back here to ask for it to be done.If to do this via VBA is too difficult
If it were me, I'd go for the compromise option (shading without merging and details in the Gantt chart), but if you want the latter, then you'll have to either do it manually or go with VBA. So what's it to be? If you want VBA, then I need to move the thread. If you want to stick to doing it manually, this thread needs marking as solved. If you want the compromise solution, then we need to get on with that. Which is it to be?
Many thanks, as usual for your quick reply.
I understand what you are saying. My main reason for automating the process was so that some of my employees could also enter a reservation and by the process being automated, I could be assured that all of the steps were executed. Manual input can lead to human error and steps in the process being forgotten.
So I am leaning more towards the VBA solution for the sole purpose of wanting to be able to see the clients name in the shaded reservation of the Gantt chart. Otherwise your compromise solution would do the trick.
Should I close this thread, therefore, and open a new one within VBA?
No - I shall move this thread to the VBA section. We do not allow thread dulpication here.
Of course I fully understand WHY you want to do this - I don't need that explaining.
An example of a macro solution can be found in the attachment.
Make sure that the dates in the Planning sheet point to the same year as in the Reservations sheet. See what happens when you don't have the dates prepared in the Planning sheet (Eric Clapton)
Hello.
Thank you so much. That is perfect.
Can I copy this macro into another worksheet as another forum expert solved another issue that I had and I would really like to combine the two worksheets as then all of my problems would be resolved.
I have attached the workbook with my other issue resolved. Adding this macro would mean my solution is, in theory, finished.
I have devastated your Planning sheet.
1. One column per day is enough.
2. With the formulas in rows 2 and 3 as well as cell A2, you can easily change the years.
3. The sheet uses conditional formatting to mark Saturdays and days in the next year.
You only need to change the value in cell A2 to have the Planning sheet prepared for the next year. With conditional formatting, the marking of Saturdays and days of the week will change automatically.
Due to the changes, the code has also been modified.
Problem noted. The customer cannot extend the stay in the same room. It is necessary to assign him another room after the end of the booked stay.
Artik
OK. Thanks for your input but the reason why I had two columns for the same day was for reservation change overs. A client leaves on a Saturday and another arrives on the same day. Having just one column per day does not reflect this....
You don't need to mark the leaving day, just the dates (nights) they are staying.
This is untrue. You used two columns for one day to be able to apply merged cells. The earliest version of my code did more or less the same thing as your merged cells. The stay started in the right cell of the day and ended in the left cell.Originally Posted by iainpeace
In the version with one column per day, the same thing happens. The bar starts in the right half of the cell and ends in the left half. If you have trouble with the perception of this solution, I can offer you another version of the code. Just replace the code in the file from post #10 with the following:Artik
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks