Is there a way to make it possible to edit one cell, then the value gets mirrored in another cell AND VICE VERSA?

Z

Zedrick13

Board Regular
Joined
Sep 8, 2018
Messages
100
In case possible, how do I make a cell's value reflect in another cell and have the capability to edit the value on either cell? For example, I have cell A1 in Sheet 1 and A2 in Sheet 2. I want it that when I put "Pass" as the value for 'Sheet1'!A1, the same value appears in 'Sheet2'!A2. Conversely, I also want to be able to modify the value of 'Sheet2'!A2 and it would reflect on 'Sheet1'!A1.

Hope this makes sense.

Thanks in advance!
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Yes, that can be done with VBA. Basically, you would have updated VBA that runs automatically when one cell is updated, and then it updates the other.
If the two cells are on different sheets, you would need two blocks of code, one for each sheet.

We can help you do that, if you can provide the following:
- Let us know exactly what sheets and ranges are involved
- Let us know the exact conditions in which the update should happen (does it only happen if the cell is updated to a particular value? what if a value is deleted, should the other cell be cleared as well?)
 
Upvote 0
Right click on the Sheet1 tab and select View Code. put this code into the VBA window.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Sheets("sheet2").Range("A1") = Sheets("Sheet1").Range("A1") Then End

Application.EnableEvents = False
If Not Intersect(Target, Sheets("Sheet1").Range("A1")) Is Nothing Then
Sheets("sheet2").Range("A1") = Sheets("Sheet1").Range("A1")
End If
Application.EnableEvents = True
 

End Sub

Right click on the Sheet2 tab and select View Code. put this code into the VBA window.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Sheets("sheet2").Range("A1") = Sheets("Sheet1").Range("A1") Then End
Application.EnableEvents = False
If Not Intersect(Target, Sheets("Sheet2").Range("A1")) Is Nothing Then
Sheets("sheet1").Range("A1") = Sheets("Sheet2").Range("A1")
End If
Application.EnableEvents = True
 

End Sub

When A1 is changed in sheet1 or sheet2 then the code will update the other sheet to the new value.
 
Upvote 0
I appreciate your response. May I know if the VBA code will still work if I upload and open the Excel spreadsheet through Google sheets?

Joe4 said:
Yes, that can be done with VBA. Basically, you would have updated VBA that runs automatically when one cell is updated, and then it updates the other.
If the two cells are on different sheets, you would need two blocks of code, one for each sheet.

We can help you do that, if you can provide the following:
- Let us know exactly what sheets and ranges are involved
- Let us know the exact conditions in which the update should happen (does it only happen if the cell is updated to a particular value? what if a value is deleted, should the other cell be cleared as well?)
Click to expand...
 
Last edited:
Upvote 0
Thank you for your response. I really appreciate the help. Can you tell me if the code added to the Excel file will still work if I upload and open it through Google sheets? Also, I am gonna need the code for me to use on multiple cells. So, how do I suppose to write the code properly in the VBA?

Scott T said:
Right click on the Sheet1 tab and select View Code. put this code into the VBA window.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Sheets("sheet2").Range("A1") = Sheets("Sheet1").Range("A1") Then End

Application.EnableEvents = False
If Not Intersect(Target, Sheets("Sheet1").Range("A1")) Is Nothing Then
Sheets("sheet2").Range("A1") = Sheets("Sheet1").Range("A1")
End If
Application.EnableEvents = True
 

End Sub

Right click on the Sheet2 tab and select View Code. put this code into the VBA window.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Sheets("sheet2").Range("A1") = Sheets("Sheet1").Range("A1") Then End
Application.EnableEvents = False
If Not Intersect(Target, Sheets("Sheet2").Range("A1")) Is Nothing Then
Sheets("sheet1").Range("A1") = Sheets("Sheet2").Range("A1")
End If
Application.EnableEvents = True
 

End Sub

When A1 is changed in sheet1 or sheet2 then the code will update the other sheet to the new value.
Click to expand...
 
Upvote 0
Can you tell me if the code added to the Excel file will still work if I upload and open it through Google sheets?
Click to expand...
I am not quite clear on what you mean here.
Is this going to be an Excel file, or a Google sheets file?
Google sheets does not use VBA, it has its own scripting language (which I am not experienced in).
 
Upvote 0
I am using Excel right now. You may check out the sample spreadsheet through the link below. However, only if possible, I also want the file available in my Google Drive and open it as a Google sheet so other people can access it and make some changes if necessary. But, if the VBA code will not work there or if you are not sure, I think I'm gonna stick with the Excel file instead.

https://www.dropbox.com/s/x1syp6w3jenhw05/Sample.xlsx?dl=0

On that Excel file, I just need it so that I can edit the 'Result' column in Sheet 1 and it will reflect the same value in the respective row in Sheet 2 under "PASS/FAIL" column and vice versa.

Joe4 said:
I am not quite clear on what you mean here.
Is this going to be an Excel file, or a Google sheets file?
Google sheets does not use VBA, it has its own scripting language (which I am not experienced in).
Click to expand...
 
Last edited:
Upvote 0
Yes, if you open it in Google sheets, I am pretty sure that the VBA code will not work.

Scott's VBA code should do what you want (in Excel). It is pretty easy to extend the code to work on a range of cells instead of just one cell, if you desire.
 
Upvote 0
I see. Is this how you extend it (just an example):

Sheet 1:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Sheets("sheet2").Range("A1","A2","A3","A4","A5") = Sheets("Sheet1").Range("A1","A2","A3","A4","A5") Then End

Application.EnableEvents = False
If Not Intersect(Target, Sheets("Sheet1").Range("A1","A2","A3","A4","A5")) Is Nothing Then
Sheets("sheet2").Range("A1","A2","A3","A4","A5") = Sheets("Sheet1").Range("A1","A2","A3","A4","A5")
End If
Application.EnableEvents = True
 

End Sub

Sheet 2

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Sheets("sheet2").Range("A1","A2","A3","A4","A5") = Sheets("Sheet1").Range("A1","A2","A3","A4","A5") Then End
Application.EnableEvents = False
If Not Intersect(Target, Sheets("Sheet2").Range("A1","A2","A3","A4","A5")) Is Nothing Then
Sheets("sheet1").Range("A1","A2","A3","A4","A5") = Sheets("Sheet2").Range("A1","A2","A3","A4","A5")
End If
Application.EnableEvents = True
 

End Sub

Joe4 said:
Yes, if you open it in Google sheets, I am pretty sure that the VBA code will not work.

Scott's VBA code should do what you want (in Excel). It is pretty easy to extend the code to work on a range of cells instead of just one cell, if you desire.
Click to expand...
 
Last edited:
Upvote 0
Not quite. Here is how I would do it:

For Sheet1 module:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'   To be placed in Sheet1 module


    Dim rng As Range
    Dim cell As Range
    Dim addr As String
    
'   Capture cells updated in our designated range
    Set rng = Intersect(Target, Range("A1:A5"))
    
'   Exit if no cells in range being updated
    If rng Is Nothing Then Exit Sub
    
    Application.EnableEvents = False


'   Loop through cells just updated in our designated range
    For Each cell In rng
'       Capture address and update corresponding cell on sheet 2
        addr = cell.Address
        Sheets("Sheet2").Range(addr) = cell
    Next cell
    
    Application.EnableEvents = True
    
End Sub
For Sheet2 module:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
'   To be placed in Sheet2 module


    Dim rng As Range
    Dim cell As Range
    Dim addr As String
    
'   Capture cells updated in our designated range
    Set rng = Intersect(Target, Range("A1:A5"))
    
'   Exit if no cells in range being updated
    If rng Is Nothing Then Exit Sub
    
    Application.EnableEvents = False


'   Loop through cells just updated in our designated range
    For Each cell In rng
'       Capture address and update corresponding cell on sheet 2
        addr = cell.Address
        Sheets("Sheet1").Range(addr) = cell
    Next cell
    
    Application.EnableEvents = True
    
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
2-Way Cell Referencing
Replies
1
Views
189
jkpieterse
jkpieterse
T
  • Solved
Apply conditional format color to each cell in a row relative to the value of each cell in a row from another sheet
2
Replies
15
Views
291
LegendaryAxe
L
L
  • Locked
  • Question Question
*Updated Question*...on creating a Bi-directional (two-way) Link in Excel that can link multiple rows and columns?? (You all are amazing!)
Replies
2
Views
413
Joe4
Joe4
G
  • Question Question
Create formula from multistep sheet
Replies
5
Views
174
Joe4
Joe4
S
  • Question Question
Formula for Pulling Data From Sheet 2 Based on Cell Value in Sheet 1
Replies
1
Views
208
Kaper
Kaper

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top