Thanks Thanks:  0
Results 1 to 9 of 9

Thread: Referenced Cells

  1. #1
    New Member
    Join Date
    Apr 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Referenced Cells

    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,

  2. #2
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    2,535
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Referenced Cells

    .
    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

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    3,265
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Referenced Cells

    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 by DanteAmor; Apr 21st, 2019 at 10:40 PM.
    Regards Dante Amor

  4. #4
    New Member
    Join Date
    Apr 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Referenced Cells

    [QUOTE=DanteAmor;5264646]


    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,

  5. #5
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    2,535
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Referenced Cells

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

    You Received An Answer - Now What ?

    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...-now-what.html

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    3,265
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Referenced Cells

    [QUOTE=zafega;5264666]
    Quote Originally Posted by DanteAmor View Post


    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
    Regards Dante Amor

  7. #7
    New Member
    Join Date
    Apr 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Referenced Cells

    [QUOTE=Logit;5264879].

    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.

  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    3,265
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    9 Thread(s)

    Default Re: Referenced Cells

    Im glad to help you and thanks for the feedback.
    Regards Dante Amor

  9. #9
    Board Regular Logit's Avatar
    Join Date
    Aug 2016
    Location
    United States
    Posts
    2,535
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Referenced Cells

    .
    zafega

    Thank you for the acknowledgement.

    Cheers.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •