Hotel room booking system

[charl40winks]

I cannot seem to find a solution to my problem no matter how long I've been pondering on it. And to be honest I'm uncertain whether a solution is possible within excel.

I'm running a small hotel of 26 rooms. I have a table in an excel sheet with three columns: guest name, check-in date, and check-out date. The table is sorted by check-in date. I want to create a fourth column in which room numbers are going to be assigned automatically. This means that once a room number is assigned, it cannot be reassigned before the checkout date of the reservation it has already been assigned. Yet, after the checkout date, the corresponding room number should become available again to be assigned to another reservation.

 

[didi1983ON]

This is really unsophisticated as a solution but I think it does what you'd like. In column E there is a drop-down. It will not show rooms where the check-out date is past today's date. It will show rooms if the check-out date is today's date or later or there is no one assigned to it. So cell C2 is =today(), Row E is a drop-down list created by going in Data->Data Validation and selecting list to reference J5:J30. Then for H is =IFERROR(INDEX(D:D,MATCH(G5,E:E,)),0) and row J is =IF(H5>$C$2,"",G5). I did change the formatting in row H to be dates but when it is a zero value it shows a number just because then you can scan that table easy if a guest calls for a specific room and say when it is open.

Sorry I'm not as savvy as some others but thought I'd give an option.

 

[charl40winks]

Thanks really appreciated will checked it out....

 

[charl40winks]

Thanks really appreciated will checked it out....

Just to be clear in Cell H5 this formula =IFERROR(INDEX(D:D,MATCH(G5,E:E,)),0)
Cell J5 this formula =IF(H5>$C$2,"",G5)

Thanks

 

[charl40winks]

Hi Thanks got it working prefect...............