How to deal with overbooking in Excel?

[Garner]

Hey everyone,

I was wondering if someone could help me. I have to basically create a booking sheet and I have a drop down menu with the room numbers, once the room type has been chosen. This is all well but I want to know is: How would I stop overbooking?

Any replies would be appreciated.

[Paul]

Hi Garner, welcome to the forum.

Let's assume you have a 100-room hotel. A2:A101 contain drop-down (data validation) lists, each with all of the room numbers, e.g. "Room1", "Room2", etc.

You can use Data Validation applied to all of those cells so that any one cannot be duplicated in the rest of the cells. So you wouldn't be able to select Room 18 in A2, and again in A90. There are some more complex methods as well, such that as soon as you pick "Room 18" in one of the cells, it would disappear from the other 99 drop-down (data validation) lists.

I assume you have a list of rooms and room types in a table somewhere. That way you could select a room from the list, and in the next column it could automatically lookup the room type based on a VLOOKUP to that table.

Hope that helps you move forward..

[Garner]

Thanks for replying. How would I use Data Validation so it can't be duplicated? I'm currently using it to give me the drop down menu.

[Paul]

I must be having a senior moment. I guess we can't have both a List validation and a formula validation to check for duplicates. So this way should work...

Continue using the List option so you can have a drop-down of all the room numbers. Then add a Worksheet_Change event to that sheet as shown below.

Right-click on the sheet tab and choose View Code. Paste the code shown below into the VB Editor. Adjust your range of cells for room numbers - the code specifies A1:A100. Close the VB Editor.

Please Login or Register  to view this content.

Hope that helps!

[Garner]

Thanks a lot for the above and sure did help. I was just wondering if that could be changed slightly so it would work on more then one table within the same sheet?

Say I have a table for each week of the month on one sheet but I only want it to warn me of the overbooking on that week and not the complete thing, if that makes any sense.

[Paul]

Assuming your weekly columns for room numbers were now A1:A100, G1:G100, M1:M100 and S1:S100, you could adjust the code as shown below to account for any of these columns, and it would only validate your room selections against the column that was changed, not all of them.

I added a String variable to extract the column that was changed (Target.Address is returned in $A$1 format, so the MID function pulls the second character). I then updated the Intersect range to include the new columns, and updated the CountIf function to include the variable column letter.

Please Login or Register  to view this content.

[Garner]

Thanks again,

That sort of works. But I select say Room 101 in one table and then 101 again in another table, it tells me it's being used. Is there no way to keep the tables separate so it will only alert me when it's used in one table each time?

[Paul]

What I provided does exactly what you asked, within the confines of my specific example. If it is not working for you I'd suggest providing a sample workbook and pointing out what needs fixing.

[Garner]

If it was rows instead of columns, would that affect what you have given me?

[Paul]

Yes. Any difference between my example and your actual layout would require that you change the code in one or more ways.

[Garner]

Yes. Any difference between my example and your actual layout would require that you change the code in one or more ways.

That would be the case then. Instead of it being columns, it's in rows and the first set will be like:

B9:B39, B46:B76

etc

Sorry I should of pointed that out in the first place

[Paul]

Yes, also why a sample workbook would have helped. Based on your new instructions, the following code should work. First, though, name your ranges of cells. For example, select B9:B39 together and then in the Name box just above column header A (where it should say B9 at this point) type Week1. Select B46:B76 and do the same, but name it Week2. Repeat for all of those ranges.

Then, update the code below to include those ranges in the first Intersect test (where you currently see "Week1,Week2".. add the rest of them). You don't have to call your ranges Week1 and Week2, etc, but name them something that makes sense, and probably in a standardized format so you know what they are for.

Please Login or Register  to view this content.

The code first checks to see if the Target cell (the cell you just changed) intersects with any of the named ranges. If so, it then proceeds to determine which named range, and assigns that range's 'Name' property to the tName variable. It then uses that variable in the COUNTIF function to see if your current entry exists in the named range more than once. If so, it undoes your change and pops up a message.

[Garner]

Thank you very much and I do appreciate your help.

--------

I've followed what you have said and checked that it was correct in the code. But when I select a room number an error comes up that says:

Run-time error '1004':

Method 'Range' of object'_Worksheet' failed

[Paul]

At this point I'm unlikely able to assist without a copy of your workbook. Please post a copy of it to this thread after removing any confidential data. I only need to see the sheet you're working on and any relevant data (can be fake, but should be present).