Meeting room management

R

Rich B

I want to set up a meeting room management system using Excel. I want to view
rooms on a daily calendar but be able to switch easily to other dates? Can
anyone give me any advice on the best way to do this?
 
A

Arvi Laanemets

Hi

Here is an example I composed on fly.

Create an empty workbook RoomManagement.xls

Create a sheet SetUp
On SetUp sheet, create an one-column table DateList
A1="DateList"
A2=TODAY()+ROW(A1)-ROW(A$1)
Format A2 as Custom "dddd dd.mm.yyyy" (or in any other valid date format you
like)
Copy A2 down for some amount of rows (it determines for how much days from
today and further you can enter dates into room booking shedule, and for how
much days you can get a daily report).
Define a named range DateList
=OFFSET(SetUp!$A$2,,,COUNTIF(SetUp!$A:$A,">0"),1)
You can hide the SetUp sheet after that, when you want.

Create a sheet Rooms
On Rooms sheet, create an one-column table RoomsList
A1="Room"
A2 and down enter rooms (names, numbers or whatever identificators) you want
to manage.
Define a named range RoomsList
=OFFSET(Rooms!$A$2,,,COUNTIF(Rooms!$A:$A,"<>")-1,1)
Again, when you don't foresee you need to update rooms list in near future,
you can hide Rooms sheet.

Create a sheet Shedule
On Shedule sheet, create a table (headers in row 1)
Entry, Date, Room, From, To, Who
Into cell A2 enter the formula
=IF(AND(B2="",A3=""),"",ROW()-ROW(A$1))
and copy the formula down for some reasonable amount of rows (let's it be #)
From cell B2 and down to row # format cells in valid date format (like "dddd
dd.mm.yyyy") and additionally as combos using data validation list with
source
=DateList.
(You also can determine error alert for data validation - like:
Title - "Input Error!"
Error Message - "You tried to enter date which doesn't fall into DateList
range!".)
From cell C2 and down to row # format cells as combos using data validation
list with source
=RoomsList
Format cells in range D2:E# as Custom "hh:mm"
Define named ranges
SheduleEntry=OFFSET(Shedule!$A$2,,,COUNTIF(Shedule!$A:$A,">0"),1)
SheduleDate=OFFSET(Shedule!$B$2,,,COUNTIF(Shedule!$A:$A,">0"),1)
SheduleRoom=OFFSET(Shedule!$C$2,,,COUNTIF(Shedule!$A:$A,">0"),1)
SheduleFrom=OFFSET(Shedule!$D$2,,,COUNTIF(Shedule!$A:$A,">0"),1)
SheduleTo=OFFSET(Shedule!$E$2,,,COUNTIF(Shedule!$A:$A,">0"),1)
SheduleWho=OFFSET(Shedule!$F$2,,,COUNTIF(Shedule!$A:$A,">0"),1)
Select the range D3:D# and apply custom data validation with formula
=(IF($B3="",0,SUMPRODUCT(--(SheduleDate=$B3),--(SheduleRoom=$C3),--(SheduleF
rom<$D3),--(SheduleTo>$D3)))<2)
Select the range E3:E# and apply custom data validation with formula
=(IF($B3="",0,SUMPRODUCT(--(SheduleDate=$B3),--(SheduleRoom=$C3),--(SheduleF
rom<$E3),--(SheduleTo>$E3)))<2)
(Check those last 2 - I myself used somewhat different formula, but I think
it'll be better. You mustn't be able enter any time, which falls into some
existing time interval for this room. And of course, it's a good idea to
determine error alert for them.)
You can hide the Entry column

Fill the shedule table. When deleting old entries, never delete row 2.
Overwrite it, or simply clear contents from it. When filling the shedule,
you don't need to follow some specific order, and you can have gaps (Entry
column of-course has non-zero value for such rows). When clearing contents
from some row, be sure, the formula in Entry column remains intact -
probably it'll be an excellent idea to protect the sheet (but then you can't
delete rows anymore, of course you can overwrite any empty rows with new
entries)

As last step, create a DailyRep sheet
B1="Room:"
B2="Date:"
Format C1 using data validation list with source
=RoomsList
Format C2 as valid date, and the using data validation list with source
=DateList
Define named ranges
Room=DailyRep!$C$1
Date=DailyRep!$C$2
Into row 4 enter headers
From, TimeInterval, Who
Into From column, enter times with some fixed interval (... or 15 min or 30
min or 1 hour or ...), like
08:00
08:15
08:30
....
18:45
19:00
Into B5 enter the formula
=IF(OR(A5="",A6=""),"",TEXT(A5,"hh:mm") & " - " & TEXT(A6,"hh:mm"))
and copy it down for same number of rows as times in column A
Into cell C5 enter the formula
=IF(SUMPRODUCT(--(SheduleDate=Date),--(SheduleRoom=Room),--(SheduleFrom<=A5)
,--(SheduleTo>=A6),SheduleEntry)=0,"",INDEX(SheduleWho,SUMPRODUCT(--(Shedule
Date=Date),--(SheduleRoom=Room),--(SheduleFrom<=A5),--(SheduleTo>=A6),Shedul
eEntry),1))
and copy it down for same number of rows as times in column A

Select room and date from dropdowns, and names who booked this room at which
time for this date, are displayed
 
Top