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]

you can use VBA (execute when cell is changed) which will check the custome ID in column A when information Yes/No is changed in column B then search this ID in other sheets and changed them as well.

 

[BGY23]

In the column B of sheet 1 do a vlookup on the customer Id in sheet 2 to bring back the resullt, When sheet 2 changes so will sheet 1

 

[skorpionkz]

BGY23 this solution would not handle this problem, as it makes Sheet 2 depends on Sheet 1 and not on each over. and if there is more than 2 sheets (let say for each department) then lookup would not be able to handle it (as long if you would not use extra column for each sheet)

 

[BGY23]

Ok, so which is the master cell/sheet. If an opertator changes say, sheet 3 to "Yes" and another operator leaves sheet 1 as "No", How do you know which is correct the Yes or the No ??

 

[skorpionkz]

dont know what TomUK will answer but it looks like last change should affect other sheets

 

[BGY23]

Funny... Skorpionkz I didn't notice you were not the original poster. If its a any sheet gets changed to "yes/No" then all say "yes/No" depending on the most recent change, then as you say vba is the solution.

 

[tomUK]

Ok, so which is the master cell/sheet. If an opertator changes say, sheet 3 to "Yes" and another operator leaves sheet 1 as "No", How do you know which is correct the Yes or the No ??

Thanks for the replies. There isn't a master - one of the two departments will compelte a task relating to a customer and will update their tab. As soon as either tab is updated to Yes, or reverted back to No, the other should follow suite.

So the latest change should be the one that takes precident and shows on both tabs. To make it a little more confusing I will need this for more than two tabs now.

you can use VBA (execute when cell is changed) which will check the custome ID in column A when information Yes/No is changed in column B then search this ID in other sheets and changed them as well.

Skorpionkz thanks for your advice but I'm not too clued up on VBA I'm afraid so unsure how to do this.

Tom

 

[skorpionkz]

Hi Tom,

Try below solution

Please TEST this FIRST in a COPY of your workbook (always make a backup
copy before trying new code, you never know what you might lose). </SPAN></SPAN>

1. Copy the below code
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Select "ThisWorkbook" Module
5. Where the cursor is flashing, paste the code
6. Press the keys ALT + Q to exit the Editor, and return to Excel
7. To run the macro made changes in Column B</SPAN>

Please note that if you will change value of cell in Column B to enything else than "Yes" or "No" the code will camunicate this and will NOT change other sheet
</SPAN>

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 Target.Column = 2 Then
    If Target.Value = "Yes" Or Target.Value = "No" Then
        strNewValue = Cells(Target.Row, 2)
        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, 2) = 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]

Hi Tom,

Try below solution

Please TEST this FIRST in a COPY of your workbook (always make a backup
copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Select "ThisWorkbook" Module
5. Where the cursor is flashing, paste the code
6. Press the keys ALT + Q to exit the Editor, and return to Excel
7. To run the macro made changes in Column B

Please note that if you will change value of cell in Column B to enything else than "Yes" or "No" the code will camunicate this and will NOT change other sheet

Thanks that worked great.

I'm thinking of adding another column or two to the worksheets (to work in the same way as column 2) and I've been playing around with the script for a while - I can get it to relate to another column fine, but not 2 or 3 at the same time as it says "For control variable already in use" when I try to add that to the script. Don't suppose this is an easy thing to do?