Need help creating a desk booking system

[MG123]

Hello All!

I am trying to create a desk booking system in excel. Unfortunately, my office does not have the budget/allow for us to have specific software.
I have 12 desks at the moment. I have created a Calendar tab and a requests tab.

My idea is to have employees only enter data in the requests tab and for this data to feed through to the Calendar tab using formulas. I would lock the Calendar tab so it cannot be altered by employees.

Issue 1:

I have tried using IF, AND, Vlookups and I can't seem to get what I need (you can see some formulas in cells G4-I4 in the calendar tab).

I created this formula below, what I think it is saying is IF the desk number AND start date AND start time slot in the requests tab match the calender then the output should be Employee (column B requests) and if it doesn't match the output should be Available (G1 calendar). This works when I put it in cell I4 (calender) but when I drag it down and across it doesn't work. I also wasn't sure how to include the end date and end time slot in the formula. I also want to be able to potentially expand the time slots to hourly slots but I am not sure how complicated the formula will be.

=IF(AND(VLOOKUP($I$3, Requests!$A:$A, 1, 0), VLOOKUP(Calendar!$E4, Requests!$C:$C,1,0), VLOOKUP(Calendar!$F4, Requests!$D:$D, 1,0)), Requests!$B3,Calendar!$G$1)

Issue 2 - In the calendar tab columns B & C I want to be able to have a snapshot of which desks are booked or available based on a date selected from a drop down. I don't know where to begin with attempting this,

Issue 3 - In the requests tab, as some employees may try to book a desk that is already booked, I want some formula or conditional formatting which will highlight the row in red if a desk is double booked based on the same desk number, start date, start time slot, end date and end time slot. Would a concatenate work here?

As you can see I have very basic skills, I can visualize what I need but lack the knowledge of formulas to execute. Any insights/help/suggestions will be greatly appreciated.
Desk booking.xlsx
Thank you!

 

[dmt32]

Hi
welcome to forum
Seem to recall a member here posted a solution for an OP some time back but cannot find the link.
What you are trying to do in excel can prove to be a lot of work but if want to pursue it suggest that you search what is already out there & see if can use as a base to build your project.

Here is one you can download for free : When a Hot Desk Booking Template in Excel Is the Way to Go
Its not a recommendation just an example of what can be found that might help you.

Hope Helpful

Dave