Dropdown list circular reference Excel VBA

Question

I am following up on a answer that has been posted before at the following link: Circular Reference with drop-down list

When the dropdown lists and sources are in the same cell on their respective sheets, the solution works. However, I'm attempting to figure out how it operates when they are not. Many thanks

I am following this answer:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Address = "$B$5" And Sh.Name <> "Sheet3" Then
        On Error GoTo bm_Safe_Exit
        Application.EnableEvents = False
        Dim w As Long
        For w = 1 To Worksheets.Count
            With Worksheets(w)
                'skip this worksheet and Sheet3
                If CBool(UBound(Filter(Array(Sh.Name, "Sheet3"), _
                        .Name, False, vbTextCompare))) Then
                    .Range("B5") = Target.Value
                    '.Range("B5").Interior.ColorIndex = 3  '<~~testing purposes
                End If
            End With
        Next w
    End If
bm_Safe_Exit:
    Application.EnableEvents = True
End Sub

I'm attempting to have two lists so that if I edit one, it will automatically update the other. How can I achieve the same outcome, for instance, if the dropdown menus are in cells A3 on Sheet1 and D9 on Sheet2?

Here is what I'm looking for: I want to create a drop-down list that says "Complete" or "Incomplete" on two sheets (sheet 1 and sheet 2). I want page 2 to state the same thing as sheet 1 if I alter it from Complete to Incomplete, but I also want the opposite to be true (If I change sheet 2 from Complete to Incomplete, I want sheet 1 to change).

Answer 1

Try this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    Dim arrCells, el, i As Long, m, tgt, arr
    
    arrCells = Array("Sheet1|D3", "Sheet2|B4") 'all cells with the list
    
    tgt = Sh.Name & "|" & Target.Address(False, False)
    m = Application.Match(tgt, arrCells, 0) 'matches one of the list cells?
    
    If Not IsError(m) Then
        On Error GoTo bm_Safe_Exit
        Application.EnableEvents = False
        For i = LBound(arrCells) To UBound(arrCells)
            If arrCells(i) <> tgt Then 'skip the cell raising the event...
                arr = Split(arrCells(i), "|")
                ThisWorkbook.Sheets(arr(0)).Range(arr(1)).Value = Target.Value
            End If
        Next i
        Application.EnableEvents = False
    End If
    
bm_Safe_Exit:
    Application.EnableEvents = True
End Sub