Worksheet cells dependant on eachother (Circular reference problem)

[tomUK]

Hi,

I've tried different ways of doing this and am stuck.

Basically I have two worksheets on a workbook. Although different, they share the same Column A (for ease of explaining we can call this a customer ID) and they share one other column, Column B, which they can only enter Yes or No. No is the default but they can change to Yes. Each worksheet is used by a different group who updates different columns.

My problem is that when one group moves a customer to 'Yes' in column B, I need the other worksheet to also move to 'Yes' in column B. And if either of them move a customer to 'No', I want the other worksheet to move to 'No' too.

The only workaround I can think of (which I don't want to do) is to create a column to the right of each column B giving a warning message that the two columns don't match so that manual changes can be made, through an IF formula. Hopefully there is an easy solutions though?

Many thanks for reading and any advice most welcome.

Tom

 

[skorpionkz]

to make sure I understand this correctly.

say you change B2 (customer 1) for "No" it sheet should change customer 1 in column B to "No", next if you change Column D to "No" (customer 1) then it should change column D in each sheet column D customer 1 to "No" etc.

is it correct?

 

[tomUK]

to make sure I understand this correctly.

say you change B2 (customer 1) for "No" it sheet should change customer 1 in column B to "No", next if you change Column D to "No" (customer 1) then it should change column D in each sheet column D customer 1 to "No" etc.

is it correct?

Yes that's right

 

[skorpionkz]

try this code. Remember to test this on the copy of your document

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
Dim ws As Worksheet, wsCur As Worksheet
Dim CustID As String
Dim dRow As Double
Dim strNewValue As String
If Target.Count = 1 Then
    If Not Target.Column = 1 Then
    If Target.Value = "Yes" Or Target.Value = "No" Then
        strNewValue = Cells(Target.Row, Target.Column)
        CustID = Cells(Target.Row, 1)
        Set wsCur = ActiveSheet
            For Each ws In Sheets
                If Not ws.Name = wsCur.Name Then
                    dRow = ws.Columns(1).Find(CustID, LookIn:=xlValues).Row
                    ws.Cells(dRow, Target.Column) = strNewValue
                End If
            Next ws
    Else
        MsgBox ("Incorrect value addded in selected cell")
        Target.Select
        Application.EnableEvents = True
        Exit Sub
    End If
    End If
End If
Application.EnableEvents = True
End Sub

 

[tomUK]

try this code. Remember to test this on the copy of your document

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
Dim ws As Worksheet, wsCur As Worksheet
Dim CustID As String
Dim dRow As Double
Dim strNewValue As String
If Target.Count = 1 Then
    If Not Target.Column = 1 Then
    If Target.Value = "Yes" Or Target.Value = "No" Then
        strNewValue = Cells(Target.Row, Target.Column)
        CustID = Cells(Target.Row, 1)
        Set wsCur = ActiveSheet
            For Each ws In Sheets
                If Not ws.Name = wsCur.Name Then
                    dRow = ws.Columns(1).Find(CustID, LookIn:=xlValues).Row
                    ws.Cells(dRow, Target.Column) = strNewValue
                End If
            Next ws
    Else
        MsgBox ("Incorrect value addded in selected cell")
        Target.Select
        Application.EnableEvents = True
        Exit Sub
    End If
    End If
End If
Application.EnableEvents = True
End Sub

Thanks a lot, that works perfectly