Populate a calendar from a table

Hi and thanks everyone.

I am creating a calendar for accommodation (arround 100 beds). Here is a sample of the main part of it. I was so happy to learn about index an match functions, very nice, until I typed this last line in yellow in the table.
Table to Calendar.png

Raymond do not appears on the calendar, it looks like the match stops at the first Room2 it meets.

Whilst I am still searching that would be such a relief if someone would tell me how to do it.

Thanks and regards
Gerard

Hi Jazzer, thanks for your quick answer!
Here is the excel file.

We receive information to fill our calendar in the form of the left table, this is why we do it this way.

Best regards
Gerard

Hi Jazzer,

I am amazed how quickly and perfectly you solved my problem. I prefer the VBA solution then formulas in all the calendar.
All the people here send you many many thanks, it is so generous of you to spend time just to help.

Can't give much in return for now
Best wishes
Gerard

I'm glad I was able to help, and glad that it did in fact solve your problem. It was kind of interesting and I always learn something myself when I do these.

BTW if your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

Thanks 12StringJazzer I was looking for the Solved button this morning.

I just like to add about the characteristics of your precious VBA:
1) if you shift the dates of the calendar, all the data in it follow which is super great. However It happens only after you do something in the left table. I didn't understand yet when the update is made.
2) if you set arrival date for Raymond on the 30/06/15 it will overlap on Nigel, which is really good for me because occasionally a new guest occupies a room the same day the previous guest leaves it. But if it is a mistake, then
it would be good to Notify: "There is an overlap, accept or cancel".
If you set Nigel's departure on the 2/07/15 it do not overlap Raymond.

I am not yet able to understand enough to solve these two questions. Can you still help?
The other codes in the file are not from me, was left from a previous download.

Gerard

We are moving from "I need help with a solution I am developing" to "My problem is getting bigger and I need someone to do the whole thing for me."

I can't make the time commitment to address the things you have listed here. Are you doing this as part of running a real business? If so, then consider paying the person who provides this tool for you. You can post your question to our Commercial Services forum, where you can offer payment to the person who provides the final solution. If you do this, make sure that you specify all your requirements up front.

In that case let me take another look. I would be happy to contribute my efforts to a good cause.

1) if you shift the dates of the calendar, all the data in it follow which is super great. However It happens only after you do something in the left table. I didn't understand yet when the update is made.

The entire calendar is updated automatically if anything in the table on the left changes. For a row in the table on the left to be considered, it must have all four columns filled in. I have added a button to manually force the entire calendar to be updated, if you change the calendar on the right. It is not easy to make this automatic because I don't know how you are going to add or delete rows, or add or delete columns. So there is a button to do it manually.

2) if you set arrival date for Raymond on the 30/06/15 it will overlap on Nigel, which is really good for me because occasionally a new guest occupies a room the same day the previous guest leaves it. But if it is a mistake, then
it would be good to Notify: "There is an overlap, accept or cancel".
If you set Nigel's departure on the 2/07/15 it do not overlap Raymond.

There are two ways to do this. The easiest one is to adopt a convention where if a person's name in a box, they have the room for that night. The last date for a person is the last night they will occupy the room. So the previous guest leaving that day would not have his name in the box. This is what I usually see when booking a hotel room. I have done this in the attached file.

The other (fancy) way to do it is to show both names in the box, with a diagonal line. I have seen this also. I am sure this would be very helpful and I'll take a look at that for tomorrow.

I have also added an overbooking check.

That's wonderfull Jazzer, it works perfectly. If on top you do the trick with the diagonal line I think she is going to faint, I'll tell her it's from Jeff. I especially appreciate the speed you can answer with. It's Jazz. Sincerely big thanks!

This isn't as clean as I would like but have a look and let me know if this is an improvement, or just creates clutter.

Yes it would be cleaner if the diagonal line would appear only on the day there is an overlap and not on all the arrival and departure dates. Yet It is very good, between this one and the previous which lists the overlaps (fantastic feature!) you have done the job.

Now I am implementing your VBA in our project and have tried to convert the way you work with columns into working with named range. i.e. in Cells(R, "A") "A" would be the range NAME (A2:A1048576). Didn't managed yet.

There is an other retreat centre which probably will be interested by this project. I emailed them this morning. It is not too fare from you http://www.peacevillageretreat.org/. So you may receive even more good vibes.

Now I am implementing your VBA in our project and have tried to convert the way you work with columns into working with named range. i.e. in Cells(R, "A") "A" would be the range NAME (A2:A1048576). Didn't managed yet.

Cells(R, "A") means "the cell in row R and column A". I do not see why you would need a name for the range A2:A1048576.

I thought it is better to work with ranges names because in case I insert a column I would have to change "A" in "B" everywhere.

The code I wrote does not actually refer to the range A:A, so one way to insulate from changes is this. Create a named range for A1. In my example I'll call it NameCol. Then in the code:

Please Login or Register  to view this content.

There are other ways to do this too, that's just the one that has the least impact on the code.

Great I try that

Yes thanks to all the answer I have been given, I have the solution to achieve the project I am working on.
Thanks a lot Jazzer