SOLVED

interesting Booking template with multiple variants in Excel

Brass 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

 

11 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 Alecs (Brass Contributor)
Solution

@Alecs 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.

@Alecs 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

@Alecs 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.

@Riny_van_Eekelen 

Can you please help me with a situation that I'm facing here?

I've shared my excel file with other pc's and it looks like my colleagues that have an older excel version (2019) are not able to work on my file (version 2021).

I've researched the error on the internet and says that your formula is not compatible with older excel versions. The formula is in cell D4

=FILTER(new!$L$2:$L$21,(new!$E$2:$E$21=$A4)*(new!$C$2:$C$21=calendar!C$2)*(new!$D$2:$D$21<>"N/A"),"")

 

Is there an alternative to this? A solution that is compatible with excel 2019 and does the same thing as the formula from above? (i've attached the excel file for testing here)

Have a nice day!

Alex

@Alecs 

Sure! The formula in D4 would then become:

=IFERROR(INDEX(new!$L$2:$L$21,MATCH(1,($A4=new!$E$2:$E$21)*(C$2=new!$C$2:$C$21),0)),"")

It might be that your 2019 users need to enter the formula with Ctrl-Shift-Enter (i.e. not just Enter). That will put curly brackets around the formula. Then, copy and paste down all rows and across to the relevant columns.

In order to tackle the exclusion of INFO for bookings with an N/A status, it would be easiest if you change the formula in "new" column L to:

=IF(D2="N/A","",CONCATENATE(H2, ",", I2, ",", J2))

 

 

@Riny_van_Eekelen
many thanks! I've tested it out just now and worked as it should. I've changed the Concatenated function as you suggested.
Can you please take a look on the new topic that I've opened? There is an problem that I'm facing and still did not managed to solve it.
here is the link: https://techcommunity.microsoft.com/t5/excel/change-cell-reference-formula-row-based-on-input/m-p/34...

Have a nice day! :)

@Alecs Glad I could help. Noticed that you already received help on your new topic.

1 best response

Accepted Solutions
best response confirmed by Alecs (Brass Contributor)
Solution

@Alecs 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.

View solution in original post