Forum Discussion
interesting Booking template with multiple variants in Excel
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
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
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 screenThe 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 attachedAlecs 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.
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
