SOLVED

interesting Booking template with multiple variants in Excel

Occasional Contributor

Hello guys,

this topic is for those of you who want to push their creativity a bit further with excel. I've tried to make this example as easier as possible with all the respect for your time and effort. The final version will be more complex. I attach the example excel file in this message. It contains all the information.

I'm working on an booking and reservation calendar for my company. I'm stuck on the main part of this excel file: to create an formula that can mark in the calendar all the given dates with the apropriate reservation information in an 12 moths period (from 01.01.2022 to 31.12.2022). No more than 12 months.

The imput is: 

floor / room / start date / end date / other info regarding reservation

The output must be:

color all the booked days in the calendar and display all the other information next to the colored cell

 

*each floor and each rooms are unique, there can't be duplicates

***the main problem here is when an booking ends today, 12.04.2022 and the next booking starts today, 12.04.2022. Should I split the cell that needs to color in 2 parts? two halfs meaning one full day? It does not need to count hours. Only days.

Is it possible do display the booking info as a note in a cell? when hold the mouse over the cell to display the content.

I've tried all kind of functions (SUMPRODUCT, IF, VLOOKUP, MATCH and index, arrays, etc) but none works and my knowledge is not enough to get trough this. Can you please help me? at least with an ideea of how could I make this work.

Many thanks in advance,

Alex

 

7 Replies
I’d do it in steps. The last step is using ‘conditional formatting’ based on text in a cell.
For the first step, arranging the data, I’d use the Filter formula. And use multiple variables, referring to cells you can adjust as needed. Once you get everything working, use Sort combined, just for order. It might take some practising, so I’d start with a small test sheet.
Simple example: =Sort(Sheet1!A:G;Sheet1!A:A=A1*Sheet1!B:B=A2*Sheet!C:C=A3).
best response confirmed by Alecs2405 (Occasional Contributor)
Solution

@Alecs2405 Not convinced that your template is the most effective way to approach this, but if it works for you, it's good enough. Played around a bit and added conditional formatting to display the status of the booking, and a FILTER function to pick-up the INFO on the first day of the booking. And it also deals with multiple bookings on one day.

 

See attached.

@Riny_van_Eekelen 

@Riny_van_Eekelen
Thank you very much for your efforts. It's perfect!!! just what I've been looking for. 

What do you mean by not the most effective way? I'm asking because I'm open to new suggestions. I've tried to make it as simple as I could. Please share with me if you got an better ideea. At least in a few sentences.

 

question please: I've noticed how it deals with multiple bookings in one day. It is just like I've imagined it. It colors with orange the date that has 2 bookings in the same day but ONLY if one of them is confirmed (green) and the another one is not (red). I'm trying now to make it work when both bookings are confirmed (green) -> to display orange color on the day 1 of the second booking (because red means = not confirmed)

I've made an example and attatched the screeshot bellow to make it more clear. It was impossible to be done or you just forgot about this scenario? just asking.

Thank you

Alecs

I understand. Yes, that's the way I've also thought about it. Looks like Riny gave me an amazing solution. I will continue to develop it forward. Thank you for your message. Much appreciated it.

@Alecs2405 Whenever I see a schedule with many colours, borders, merged cells and must use rather complicated conditional formatting formulae to achieve results that should be relatively easy, my gut feeling says it should be done in another way. Right now I haven't really thought about how, though I wonder why you can't use a regular calendar app that comes with e.g. Outlook and create a separate calendar for each room and display them side-by-side. But, no offence. If it works for you, that's great.

 

With regard to double bookings, the orange colour is just a marker that there is a double booking that day. The colour directly above it tells you is the booking that ends that day was confirmed or not, as the colour directly below it tells you the status of the booking that starts that day. Obviously, if you have a booking for the morning only and another for the afternoon only, you can not show different colours and infos for both of them in one cell.

 

And that's probably where the design of the schedule fails. You should allow for booking partial days to avoid overlapping, if that's a real problem. So, split each day in at least two cells (AM, PM). But that might only complicate the matter, I think, when it comes to all the required formatting.

 

@Riny_van_Eekelen Now I understand. Yes, it could have been more simpler and I will think about this in the future for the any new updates to the current template. For now I think it works very good for what I need with many thanks to you.
______________________________
I would like to ask another question without opening another topic:
- is it possible to display the content of a cell as an comment that refreshes automatically when the content of the cell is updated?
here is an example:
I have written some random words in each of these cells: A1, A2, A3
and used an CONCATENATE formula in cell B1 to combine A1, A2 and A3 contents in one single cell (B1)
I would like to display the content of B1 as an comment. With the following needs:
- to update automatically if cells A1, A2 or A3 change their contents
- not to show comment if A1, A2, A3 are empty (it's not a must, but nice to have)
- to show comment only if the pointer (mouse) is held above the cell and hide if the pointer is moved to another location on the screen

 

The reason why I need to do this is because cell B1 will have a long string of caracteres that does not fit in one cell. It is not a must to be an comment. But I've searched all over the internet to try to display the output of an CONCATENATE formula in the excel formula bar, not only in the cell as an value, but without success.

 

I've came across many Macros but none of them does what I need. The only ones are in this amazing article but when tried to implement them did not work. Or may be because I didn't worked with macros before. Here is exactly what I need: https://excel.tips.net/T003374_Placing_Formula_Results_in_a_Comment.html
Can you please help with an advice? there can be many columns with comments. Not just one cell as showed in the screenshot attached

@Alecs2405 Placing formula results in a comment is apparently possible with VBA, but that's outside my comfort zone. So I can't help you with that.