Hi everyone!
I have created an Excel Conference Room Scheduler Calendar and solved the function problem with the help of one of the gurus here. Now, I tried to import the Excel file to a Google Sheet for the use of other staff in case they need to book a meeting and the conference room. The problem is, that the desired output doesn't reflect in the Calendar Sheet and shows blank results.
The Excel formula is: =IFERROR(IF(F9="","",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)=F9),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))),"")
After importing to Google Sheet, the formula became: =ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(IF(F9="","",LET(_xlpm.x,_xlws.FILTER(Scheduler!$B$9:$H$60,Scheduler!$B$9:$B$60<>"Vacant"),_xlpm.dt,INDEX(_xlpm.x,,4)+INDEX(_xlpm.x,,5),_xlpm.s,SORTBY(_xlpm.x,_xlpm.dt,1),_xlpm.ff,_xlws.FILTER(_xlpm.s,INDEX(_xlpm.s,,3)=F9),_xlpm.f,_xlws.FILTER(_xlpm.ff,INDEX(_xlpm.ff,,2)<>0),"• "&INDEX(_xlpm.f,,2)&" "&TEXT(INDEX(_xlpm.f,,4),"hh:mmAM/PM")&" to "&TEXT(INDEX(_xlpm.f,,5),"hh:mmAM/PM")&" - "&INDEX(_xlpm.f,,6)&" "&INDEX(_xlpm.f,,7))),"")), 5, 1)
attached is the Excel file.
and here is the Spreadsheet link: https://docs.google.com/spreadsheets...f=true&sd=true
Bookmarks