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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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

O
  • Question
Match dynamic value in another sheet and return values in that column as a drop down list
Replies
3
Views
375
duggie33
D
H
  • Solved
Create reference (tab name) based on cell values to create the link
Replies
4
Views
286
Fluff
Fluff
D
  • Solved
excel formula to equal data from another sheet based on cell value
Replies
2
Views
701
demonicsoul
D
P
  • Question
Userform Listbox Edit Rows Within Sheet
Replies
1
Views
432
pure vito
P
D
  • Question
VBA Code To Transfer Numerical Value To Another Group Of Cells On Another Sheet
Replies
3
Views
287
bebo021999
B

Forum statistics

Threads
1,214,813
Messages
6,121,706
Members
449,049
Latest member
THMarana

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