Referenced Cells

zafega

New Member
Joined
Apr 10, 2019
Messages
8
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,
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,114
.
In the Sheet 1 module :

Code:
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 :

Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
11,238
Office Version
2007
Platform
Windows
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.


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
 
Last edited:

zafega

New Member
Joined
Apr 10, 2019
Messages
8
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

Well-known Member
Joined
Aug 31, 2016
Messages
3,114
.
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

Well-known Member
Joined
Dec 3, 2018
Messages
11,238
Office Version
2007
Platform
Windows
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:

Code:
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

New Member
Joined
Apr 10, 2019
Messages
8
.

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

Well-known Member
Joined
Dec 3, 2018
Messages
11,238
Office Version
2007
Platform
Windows
Im glad to help you and thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,096,068
Messages
5,448,194
Members
405,492
Latest member
DPuser

This Week's Hot Topics

Top