Referenced Cells

[zafega]

Hi all,

I have a tricky question regarding linking two cells in different sheets.

I have a column of data in a worksheet (say Sheet1) that I need to be reflected in another worksheet (say Sheet2).
So far I have no trouble.
The difficult part is that I would like to be able to change the data in sheet2 and be able to see this change in sheet1, as well as I change something in Sheet1 I would like to see the change in Sheet2.

I imagine that this involve some auxiliary cell and some if formula, but I cannot figure it out how to get there.

Any help is very much appreciate it.

Best regards,

 

[Logit]

.
In the Sheet 1 module :

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r1 As Range, r2 As Range
    Set r1 = Sheets("Sheet1").Range("A1:Z100")
    Set r2 = Sheets("Sheet2").Range("A1:Z100")
    If Intersect(Target, r1) Is Nothing Then Exit Sub
    Application.EnableEvents = False
        r2.Value = r1.Value
    Application.EnableEvents = True
End Sub

In the Sheet 2 module :

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r1 As Range, r2 As Range
    Set r1 = Sheets("Sheet2").Range("A1:Z100")
    Set r2 = Sheets("Sheet1").Range("A1:Z100")
    If Intersect(Target, r1) Is Nothing Then Exit Sub
    Application.EnableEvents = False
        r2.Value = r1.Value
    Application.EnableEvents = True
End Sub

 

[DanteAmor]

You can not have a formula and a value in a cell.
If you want to see the same data in both sheets, you must do it with a macro.

For example:

Put the following code in the Thisworkbook events.

- Open the VB Editor (press Alt + F11).
- Over in the Project Explorer, double click on ThisWorkbook.
- In the white panel that then appears, paste the below code.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Application.ScreenUpdating = False
    If Sh.Name = "Sheet1" Then
        Sheets("Sheet2").Range("A1:A" & Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row).Copy
        Sh.Range("A1").PasteSpecial xlPasteValues
        Range("A1").Select
    ElseIf Sh.Name = "Sheet2" Then
        Sheets("Sheet1").Range("A1:A" & Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row).Copy
        Sh.Range("A1").PasteSpecial xlPasteValues
        Range("A1").Select
    End If
End Sub

 

[zafega]

Thanks @DanteAmor for the reply.
I tested the formula in a blank file and works beautifully.

I tried to change it to accommodate the file layout where I am working with, and I couldn't make it work.
The starting cell in the Sheet1 is from the G12, and in the Sheet2 are from column and cell N12.
If you can help me also with that it would be great.

Thanks again for the support.

Best regards,

 

[Logit]

.
And once again, although the suggestion was not utilized, a contributor is totally ignored for their contribution.

[h=2]You Received An Answer - Now What ?[/h]

Whether the answer you receive solves your issue or it doesn't ... it is rude to ignore
those who volunteered to assist you.

Let the volunteers know if their assistance was successful or not. DO NOT simply 'walk
away' and not respond.

Participation in a FORUM is the same as having a face to face conversation. How would you
feel if the person you are having a conversation with suddenly walked away without saying
anything ?

Dude ... don't be rude.

https://www.excelforum.com/the-water-cooler/1271676-you-received-an-answer-now-what.html
​

 

[DanteAmor]

Thanks @DanteAmor for the reply.
I tested the formula in a blank file and works beautifully.

I tried to change it to accommodate the file layout where I am working with, and I couldn't make it work.
The starting cell in the Sheet1 is from the G12, and in the Sheet2 are from column and cell N12.
If you can help me also with that it would be great.

Thanks again for the support.

Best regards,

Try this:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Application.ScreenUpdating = False
    If Sh.Name = "Sheet1" Then
        Sheets("Sheet2").Range("N12:N" & Sheets("Sheet2").Range("N" & Rows.Count).End(xlUp).Row).Copy
        Sh.Range("G12").PasteSpecial xlPasteValues
        Range("A1").Select
    ElseIf Sh.Name = "Sheet2" Then
        Sheets("Sheet1").Range("G12:G" & Sheets("Sheet1").Range("G" & Rows.Count).End(xlUp).Row).Copy
        Sh.Range("N12").PasteSpecial xlPasteValues
        Range("A1").Select
    End If
End Sub

 

[zafega]

.

Dear @Logit
Please do not take it personally.
I followed @DanteAmor reply because in his reply was explained step by step how to input all the data.
Since I am a beginner I found it easier.
Non the less I appreciate very much the help everybody is giving here.
This is a fantastic community.

 

[DanteAmor]

Im glad to help you and thanks for the feedback.

 

[Logit]

.
zafega

Thank you for the acknowledgement.

Cheers.