Link Cells to Change Cell Value from Many Cells

[KrisKiel]

I don't actually know how to word this question. I'm not looking to simply link cells together with an = or similar function.

Is there a way to set up cells so that if I change the value in any of those cells, they all change? Ideally, they will link across sheets.

Example: A1, B2, and C3 are linked in this way.
So if A1 has '6' entered, B2 and C3 will also display 6. Then if I change B2 to 9, A1 and C20 also change to 9.

I'm guessing it will have to be a macro instead of a formula.

 

[Tetra201]

Try placing the following sub in your worksheet's code (not in a regular module):

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Address(0, 0)
        Case "A1", "B2", "C3"
            Application.EnableEvents = False
            Range("A1,B2,C3") = Target.Value
            Application.EnableEvents = True
    End Select
End Sub

 

[KrisKiel]

Well that was the most perfect solution ever. Worked like a charm! Thank you

 

[Tetra201]

Glad to have helped.

 

[KrisKiel]

Thought I could enter the Sheet name + specific cell in place of those cells - quickly found out it is not that simple. How would I get those cells linked like that if they're all on different sheets (using the hidden sheet name)?

 

[Tetra201]

Try placing the following sub in ThisWorkbook code (not in a regular module):

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
    Application.EnableEvents = False
    Select Case Sh.Name
        Case "Sheet1"
            If Source.Address(0, 0) = "A1" Then
                Sheets("Sheet2").Range("B2") = Source.Value
                Sheets("Sheet3").Range("C3") = Source.Value
            End If
        Case "Sheet2"
             If Source.Address(0, 0) = "B2" Then
                Sheets("Sheet1").Range("A1") = Source.Value
                Sheets("Sheet3").Range("C3") = Source.Value
            End If
       Case "Sheet3"
             If Source.Address(0, 0) = "C3" Then
                Sheets("Sheet1").Range("A1") = Source.Value
                Sheets("Sheet2").Range("B2") = Source.Value
            End If
    End Select
    Application.EnableEvents = True
End Sub

 

[KrisKiel]

So that works if the sheets have those Names but not if the Names are changed (which I'm expecting to happen regularly). What about for the sheets' CodeName ?

 

[Tetra201]

Here is a sub that uses codenames:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
    Application.EnableEvents = False
    Select Case Sh.CodeName
        Case "Sheet1"
            If Source.Address(0, 0) = "A1" Then
                Sheet2.Range("B2") = Source.Value
                Sheet3.Range("C3") = Source.Value
            End If
        Case "Sheet2"
            If Source.Address(0, 0) = "B2" Then
                Sheet1.Range("A1") = Source.Value
                Sheet3.Range("C3") = Source.Value
            End If
       Case "Sheet3"
            If Source.Address(0, 0) = "C3" Then
                Sheet1.Range("A1") = Source.Value
                Sheet2.Range("B2") = Source.Value
            End If
    End Select
    Application.EnableEvents = True
End Sub