How do you Link Cells Between Sheets in same work book to change each cell value if either is changed?

[stephkc24]

Hi,

I'm new to VBA and was wondering if someone could help me out with the below?

I have two Sheets "Summary" and "Worksheet", both in the same workbook

I have cell Summary->C10 that i would like to link to Worksheet->J3 so that either cell can be modified and the other is updated.

I want to apply the above to all these cells as well:
* Summary->C10 that i would like to link to Worksheet->J3
* Summary->C11 that i would like to link to Worksheet->K3
* Summary->C12 that i would like to link to Worksheet->L3
* Summary->C13 that i would like to link to Worksheet->u3
* Summary->C14 that i would like to link to Worksheet->V3

Any help on how to accomplish this would be greatly appreciated

 

[My Aswer Is This]

If on any sheet I enter=Apha!A1 the results will be put in that cell.
Or if I enter Bravo!G12 any where I enter the value I will get the results
And the results will update any time the value changes

 

[Michael M]

??

 

[Michael M]

Maybe this in the "Summary" Sheet module

Sub worksheet_change(ByVal target As Range)
If Intersect(target, Range("C10")) Is Nothing Then Exit Sub
Sheets("Worksheet").Range("J3").Value = target.Value
End Sub

and this in the "worksheet" module

Sub worksheet_change(ByVal target As Range)
If Intersect(target, Range("J3")) Is Nothing Then Exit Sub
Sheets("Summary").Range("C10").Value = target.Value
End Sub

 

[My Aswer Is This]

Maybe this in the "Summary" Sheet module

Sub worksheet_change(ByVal target As Range)
If Intersect(target, Range("C10")) Is Nothing Then Exit Sub
Sheets("Worksheet").Range("J3").Value = target.Value
End Sub

and this in the "worksheet" module

Sub worksheet_change(ByVal target As Range)
If Intersect(target, Range("J3")) Is Nothing Then Exit Sub
Sheets("Summary").Range("C10").Value = target.Value
End Sub

I thought of this myself. But I got the ideal was the user wanted Something like this to happen no matter what sheet he was on or what cell . But we will see if this works for him

 

[My Aswer Is This]

I like my suggestion for several reasons:


Lets say you want to keepup with the Sum of Range("A1:A20) on any sheet
Put this formula in any cell on any sheet
=Sum Range("A1:A20")
Lets say I enter that formula in sheet named
"Alpha" Range("G1")
Then on any sheet in any cell I can enter: =Alpha!G1
And I get the results of the formula which may be 356.25
And this is always updated any time the Sum of the range Changes
And this does not require Vba

​

​ ​

 

[stephkc24]

Maybe this in the "Summary" Sheet module

Sub worksheet_change(ByVal target As Range)
If Intersect(target, Range("C10")) Is Nothing Then Exit Sub
Sheets("Worksheet").Range("J3").Value = target.Value
End Sub

and this in the "worksheet" module

Sub worksheet_change(ByVal target As Range)
If Intersect(target, Range("J3")) Is Nothing Then Exit Sub
Sheets("Summary").Range("C10").Value = target.Value
End Sub

This does work except that i get the following run time error "Method 'Range' of object '_Worksheet' failed.

Also how do i change the above function to link other cells as well
* Summary->C10 that i would like to link to Worksheet->J3
* Summary->C11 that i would like to link to Worksheet->K3
* Summary->C12 that i would like to link to Worksheet->L3
* Summary->C13 that i would like to link to Worksheet->u3
* Summary->C14 that i would like to link to Worksheet->V3

Thanks again for your help I'm new to VBA and trying to learn

 

[stephkc24]

This does work except that i get the following run time error "Method 'Range' of object '_Worksheet' failed.

Also how do i change the above function to link other cells as well
* Summary->C10 that i would like to link to Worksheet->J3
* Summary->C11 that i would like to link to Worksheet->K3
* Summary->C12 that i would like to link to Worksheet->L3
* Summary->C13 that i would like to link to Worksheet->u3
* Summary->C14 that i would like to link to Worksheet->V3

Thanks again for your help I'm new to VBA and trying to learn

I found the issue, with the above code provided when i was in "Summary" sheet for example it would run the VBA code and switch the corresponding value in "Worksheet" and trigger that VAB code
again. Simply corrected by doing this, And yes i did change the name of the second sheet from "Worksheet" to "DataTable"

In the "Summary" Sheet module:

Private Sub worksheet_change(ByVal target As Range)
If ActiveSheet.Name = "Summary" Then
    If Not Intersect(target, Range("C10")) Is Nothing Then
         Sheets("DataTable").Range("J3").Value = target.Value
    Else
        If Not Intersect(target, Range("C11")) Is Nothing Then
            Sheets("DataTable").Range("k3").Value = target.Value
        Else
            If Not Intersect(target, Range("C12")) Is Nothing Then
                Sheets("DataTable").Range("l3").Value = target.Value
            Else
                If Not Intersect(target, Range("C13")) Is Nothing Then
                    Sheets("DataTable").Range("u3").Value = target.Value
                Else
                    If Not Intersect(target, Range("C14")) Is Nothing Then
                        Sheets("DataTable").Range("v3").Value = target.Value
                    End If
                End If
            End If
        End If
    End If
End If
End Sub

In the "DataTable" Sheet Module:

Private Sub worksheet_change(ByVal target As Range)
If ActiveSheet.Name = "DataTable" Then
    If Not Intersect(target, Range("j3")) Is Nothing Then
        Sheets("Summary").Range("c10").Value = target.Value
    Else
        If Not Intersect(target, Range("k3")) Is Nothing Then
            Sheets("Summary").Range("c11").Value = target.Value
        Else
            If Not Intersect(target, Range("l3")) Is Nothing Then
                Sheets("Summary").Range("c12").Value = target.Value
            Else
                If Not Intersect(target, Range("u3")) Is Nothing Then
                    Sheets("Summary").Range("c13").Value = target.Value
                Else
                    If Not Intersect(target, Range("v3")) Is Nothing Then
                        Sheets("Summary").Range("c14").Value = target.Value
                    End If
                End If
            End If
        End If
    End If
End If
End Sub

 

[Peter_SSs]

@stephkc24
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug. My signature block below has more details. I have added the tags for you this time.

I also suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[My Aswer Is This]

Here is the way I would do it:
But not sure about what sheet to put it in.
This is a Example of how to do it with Case

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  6/26/2022  8:02:43 AM  EDT
If ActiveSheet.Name = "DataTable" Then
   
    Select Case Target.Address
        Case "$J$3": Sheets("Summary").Range("C10").Value = Target.Value
        Case "$K$3": Sheets("Summary").Range("C11").Value = Target.Value
        Case "$I$3": Sheets("Summary").Range("C12").Value = Target.Value
        Case "$U$3": Sheets("Summary").Range("C13").Value = Target.Value
        Case "$V$3": Sheets("Summary").Range("C14").Value = Target.Value
   End Select
End If
End Sub

 

[orthoimplantsgu]

I have the same question but not solved