Thread: How to deal with overbooking in Excel?

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..

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.

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.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
    If Application.WorksheetFunction.CountIf(Range("A1:A100"), _
        Target.Value) > 1 Then
            MsgBox "That room number (" & Target.Value & _
            ") has already been used.  Please select " & _
            "another room number from the list."
            Application.EnableEvents = False
            Application.Undo
            Application.EnableEvents = True
    End If
End If
End Sub

Hope that helps!

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.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCol As String
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:A100, G1:G100, M1:M100, S1:S100")) Is Nothing Then
    myCol = Mid(Target.Address, 2, 1)
    If Application.WorksheetFunction.CountIf(Range(myCol & "1:" & myCol & "100"), _
        Target.Value) > 1 Then
            MsgBox "That room number (" & Target.Value & _
            ") has already been used.  Please select " & _
            "another room number from the list."
            Application.EnableEvents = False
            Application.Undo
            Application.EnableEvents = True
    End If
End If
End Sub

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.

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

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.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim nm As Name, tName As String

If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("Week1,Week2")) Is Nothing Then
    On Error Resume Next
    For Each nm In ThisWorkbook.Names
        If Not Intersect(Target, nm.RefersToRange) Is Nothing Then
            tName = nm.Name
            Exit For
        End If
    Next nm
    On Error GoTo 0
    If Application.WorksheetFunction.CountIf(Range(tName), Target.Value) > 1 Then
        MsgBox "Room number " & Target.Value & " has already been used.  " & _
               "Please select another room number from the list."
        Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True
    End If
End If
End Sub

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.

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).