Conference Room Calendar

Hello care29,

Welcome to Excelforum. Be a part of large Excel community. Enjoy Learning.

Hi!

Just an update, I changed my formula from index match to Sort and Filter instead. Though it is working now, I have a minor problem. The filter gets the first scheduled line regardless of the time instead of the supposedly earliest time. To be exact, it's taking 1PM and later first than morning time until 12PM.

Here's the formula now I am using. =SORT(UNIQUE(FILTER(("• "&Scheduler!$C$9:$C$54&" "&TEXT(Scheduler!$E$9:$E$54,"hh:mmAM/PM")&" to "&TEXT(Scheduler!$F$9:$F$54,"hh:mmAM/PM")&" - "&Scheduler!$G$9:$G$54&" "&Scheduler!$H$9:$H$54),Scheduler!$D$9:$D$54=F9,"")))

Attachment 858827Attachment 858828

Selecting either Attachment 858827 or Attachment 858828 results in the following message: "Invalid Attachment specified. If you followed a valid link, please notify the administrator".
Information on attachments is given in the "HOW TO ATTACH YOUR SAMPLE WORKBOOK" banner at the top of the page.
Let us know if you have any questions.

1. Attach a sample WORKBOOK, not a screenshot.
2. Which version of Excel are you using? There is no Version 10.

OK - then please update your forum profile NOW.

What is the problem with the attached workbook? In the workbook, you need to mock up manually the results that you want to see where you want to see them.

Try this:

=LET(dt,$F$5:$F$14+$G$5:$G$14,s,SORTBY($E$5:$J$14,dt,1),f,FILTER(s,$F$5:$F$14=K18),"• "&INDEX(f,,1)&" "&TEXT(INDEX(f,,3),"hh:mmAM/PM")&" to "&TEXT(INDEX(f,,4),"hh:mmAM/PM")&" - "&INDEX(f,,5)&" "&INDEX(f,,6))

And please update your forum profile ASAP.

Provide a workbook showing the error. It works fine in your sample file ...

f is the filtered and sorted range - INDEX(f,,1) is the first column of that range, INDEX(f,,2) is the second and so on.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

Here you go:

=LET(dt,Scheduler!$D$9:$D15+Scheduler!$E$9:$E15,s,SORTBY(Scheduler!$C$9:$H15,dt,1),ff,FILTER(s,Scheduler!$D$9:$D15=F9),f,FILTER(ff,INDEX(ff,,1)<>0),"• "&INDEX(f,,1)&" "&TEXT(INDEX(f,,3),"hh:mmAM/PM")&" to "&TEXT(INDEX(f,,4),"hh:mmAM/PM")&" - "&INDEX(f,,5)&" "&INDEX(f,,6))

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

No response ... Are you happy with the tweak? Please sign off the thread as requested in my last post.

Sorry. I'm still trying to understand your formula. Since I want to expand the date range to C9:H54 not just to row 15 and regardless if the cells are empty

I fixed the empty cell issue in post #17.

Since I want to expand the date range to C9:H54 not just to row 15

What does this mean? Where have you shown this? Have you copied and pasted the formula? I can't fix what I can't see. You aren't communicated fully or clearly here. I can't read minds - sorry.

I have copied the formula for you, added a check for blank date cells and added an error trap.

In the attached, in column M onwards, I have shown what each of the formula sections that I have added do. I don't think I can be more explicit.

Please have a look.

This formula works well, =LET(dt,Scheduler!D9:D14+Scheduler!E9:E14,s,SORTBY(Scheduler!C9:H14,dt,1),f,FILTER(s,Scheduler!D9:D14=F9),"• "&INDEX(f,,1)&" "&TEXT(INDEX(f,,3),"hh:mmAM/PM")&" to "&TEXT(INDEX(f,,4),"hh:mmAM/PM")&" - "&INDEX(f,,5)&" "&INDEX(f,,6)) in reflecting the Schedules from the Scheduler Sheet into the Calendar Sheet. However, if I enter another schedule in Row 15 for the same date in the Scheduler Sheet, it will not be reflected in the Calendar Sheet as the formula is fixed to Row 14 only, hence I'm only getting a "• 12:00AM to 12:00AM" result error (Refer to the attached file Calendar Sheer). If you check the Booking table in the Scheduler Sheet, the table runs until Row 54 so I want the formula fixed until Row 54 for it to reflect the Booking data into the Calendar Sheet based on the Criteria Date.

I hope you get what I mean and thank you for your patience.

PS. I tweak the above formula to =LET(dt,Scheduler!D9:D54+Scheduler!E9:E54,s,SORTBY(Scheduler!C9:H54,dt,1),f,FILTER(s,Scheduler!D9:D54=F9),"• "&INDEX(f,,1)&" "&TEXT(INDEX(f,,3),"hh:mmAM/PM")&" to "&TEXT(INDEX(f,,4),"hh:mmAM/PM")&" - "&INDEX(f,,5)&" "&INDEX(f,,6)) that's why it's returning "• 12:00AM to 12:00AM", maybe due to the blank cells.

Also, I think there's something wrong with the formula =IFERROR(IF(F9="","",LET(dt,Scheduler!$D$9:$D$15+Scheduler!$E$9:$E$15,s,SORTBY(Scheduler!$C$9:$H$15,dt,1),ff,FILTER(s,Scheduler!$D$9:$D15=F9),f,FILTER(ff,INDEX(ff,,1)<>0),"• "&INDEX(f,,1)&" "&TEXT(INDEX(f,,3),"hh:mmAM/PM")&" to "&TEXT(INDEX(f,,4),"hh:mmAM/PM")&" - "&INDEX(f,,5)&" "&INDEX(f,,6))),"") as it's not reflecting all the Scheduled meetings for February 7 in the Calendar Sheet.

Yes, it is: there are three meetings for 7 February and they are all there:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	F
10	• R1 09:00AM to 10:00AM - RSPM MANCOM MEETING
11	• R1 01:30PM to 02:30PM - RSPM MANCOM MEETING
12	• R1 10:00AM to 11:00AM - RYR MEETING WITH THE POD

Sheet: Calendar

Once again, if you have a problem, then provide a workbook that shows it. If the range is going to be bigger than Scheduler!$C$9:$H15, then you need to extend the range accordingly, so wherever row 15 is stated in the formula, make it a much higher row to cover all eventualities.

Last attempt:

=IFERROR(IF(D3="","",LET(x,FILTER(Scheduler!$B$9:$H$60,Scheduler!$B$9:$B$60<>"Vacant"),dt,INDEX(x,,4)+INDEX(x,,5),s,SORTBY(x,dt,1),ff,FILTER(s,INDEX(s,,3)=D3),f,FILTER(ff,INDEX(ff,,2)<>0),"• "&INDEX(f,,2)&" "&TEXT(INDEX(f,,4),"hh:mmAM/PM")&" to "&TEXT(INDEX(f,,5),"hh:mmAM/PM")&" - "&INDEX(f,,6)&" "&INDEX(f,,7))),"")

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

I don't know. Why would I have tried that? Have you tried it?

This is beyond the scope of this thread. If you need further help to convert it for GoogleSheets, then open a new thread in the Other Platforms forum section.