Mirror cells so that a change in either cell updates the other
I have 2 cells containing the same picklist values on separate sheets. I want the cells to mirror each other so that whenever I choose a new value in either sheet 1 or sheet 2, the value on the other sheet is updated to match the newly selected value. Is this possible?
UPDATE
-
Worksheet 1:
- Worksheet Name: 'Live Forecast Tool'
- Cell containing picklist: C3:D3 (merged)
- Picklist values within range: ='SF MRF'!H5:Y5
-
Worksheet 2:
- Worksheet Name: 'AH'
- Cell containing picklist: C3:D3 (merged)
- Picklist values within range: ='SF MRF'!H5:Y5
Cell C3:D3 needs to update to show the value that has been changed on the other sheet.
Solution 1:
In the AH code area enter the following event macro:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r1 As Range, r2 As Range
Set r1 = Range("C3")
Set r2 = Sheets("Live Forecast Tool").Range("C3")
If Intersect(Target, r1) Is Nothing Then Exit Sub
Application.EnableEvents = False
r2.Value = r1.Value
Application.EnableEvents = True
End Sub
In the Live Forecast Tool code area enter the following event macro:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r1 As Range, r2 As Range
Set r1 = Range("C3")
Set r2 = Sheets("AH").Range("C3")
If Intersect(Target, r1) Is Nothing Then Exit Sub
Application.EnableEvents = False
r2.Value = r1.Value
Application.EnableEvents = True
End Sub