Schedule Conflict code

[obiwanvaldez]

Hi all! I am not really sure what to call what I am looking for, so if this has been asked before I apologize. I am making a schedule each month and I want to make sure I dont schedule people in more than one department. Is there a function that will allow me to type in a name on one sheet and it will show me if there is a conflict in another sheet? For instance -

Worksheet 1

Jan 1 John Doe Mike Doe Jim Doe

Worksheet 2

Jan 1 Sally Doe Jim Doe Bob Doe

In the above I would have a conflict for Jim Doe working two departments on the same day. Is there a way I can be alerted each time I type in a name and there is a conflict in another cell, or in another cell on another worksheet? Thanks in advance for all of your help!

 

[mumps]

It would be difficult to suggest a possible solution without more information. We would need to know how data on each sheet is organized. Can you post a screen shot of what your data looks like in each sheet? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[obiwanvaldez]

Ok it would be something like this. Does that help?

Schedule 1 (Worksheet 1)
A B C D
1 Date Auditorium Entrance Assistant

2
3 Jan 1 John Doe Jim Doe Mary Doe



Schedule 2 (Worksheet 2)
A B C D
1 Date Auditorium Entrance Assistant

2
3 Jan 1 Bob Doe John Doe Sally Doe

 

[mumps]

Copy and paste this macro into the worksheet code module for "Sheet1". Do the following: right click the tab for "Sheet1" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:D")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim LastRow As Long, rName As Range, ws As Worksheet
    For Each ws In Sheets(Array("Sheet1", "Sheet2"))
        LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        If ws.Name = "Sheet1" Then LastRow = LastRow - 1
        Set rName = ws.Range("B3:D" & LastRow).Find(Target, LookIn:=xlValues, lookat:=xlWhole)
        If Not rName Is Nothing Then
            MsgBox ("You have entered a duplicate name." & Chr(10) & "The duplicate name is in cell " & rName.Address(0, 0) & " in " & ws.Name & ".")
        End If
    Next ws
    Target.Select
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Copy and paste this macro into the worksheet code module for "Sheet2". Do the following: right click the tab for "Sheet2" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:D")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim LastRow As Long, rName As Range, ws As Worksheet
    For Each ws In Sheets(Array("Sheet1", "Sheet2"))
        LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        If ws.Name = "Sheet2" Then LastRow = LastRow - 1
        Set rName = ws.Range("B3:D" & LastRow).Find(Target, LookIn:=xlValues, lookat:=xlWhole)
        If Not rName Is Nothing Then
            MsgBox ("You have entered a duplicate name." & Chr(10) & "The duplicate name is in cell " & rName.Address(0, 0) & " in " & ws.Name & ".")
        End If
    Next ws
    Target.Select
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Enter the date in column A of either sheet first and then the names in columns B, C and D. Exit the cell after entering the data.

 

[obiwanvaldez]

That works great! I only have one problem and maybe it's an easy fix (By the way that macro was awesome! Thank you so much!) My problem is lets say I only want the conflict showing on given dates. For instance, if I pop John Doe in twice on Jan 1. However, if I pop John Doe on Jan 1 and then again on Jan 3 and Jan 8 and Jan 12, it wont show a conflict. Hope that made sense. Thanks again for your help!

 

[mumps]

Replace this part of the code in both macros:

If Not rName Is Nothing Then
      MsgBox ("You have entered a duplicate name." & Chr(10) & "The duplicate name is in cell " & rName.Address(0, 0) & " in " & ws.Name & ".")
End If

with this:

If Not rName Is Nothing Then
     If ws.Cells(rName.Row, 1) = Cells(Target.Row, 1) Then
          MsgBox ("You have entered a duplicate name." & Chr(10) & "The duplicate name is in cell " & rName.Address(0, 0) & " in " & ws.Name & ".")
     End If
End If

 

[obiwanvaldez]

Works great! Thank you so much for your help!

 

[mumps]

You are very welcome.