Worksheet cells dependant on eachother (Circular reference problem)

tomUK

New Member
Joined
Apr 4, 2014
Messages
5
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
 
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?
 
Upvote 0

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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
 
Upvote 0
try this code. Remember to test this on the copy of your document

Code:
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
 
Upvote 0
try this code. Remember to test this on the copy of your document

Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,747
Messages
6,180,712
Members
452,995
Latest member
isldboy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top