Excel cells sharing same underlying data

Question

I am wondering if anyone knows a way to implement the following behavior in Excel.

• You have two cells, say A4 and B6
• Whenever the user enters a value into A4, then the same value appears in B6
• Whenever the user enters a value into B6, then the same value appears in A4
• The behavior doesn't change if user enters a value. A user can enter values into either or both cells arbitrary numbers of times and always it will still appear in the other cell
• The update to the other cell is instantaneous, even if autocalc is turned off
• In general, the cells A4 and B6 always have the same value, and any time a user enters a value into either one, the same value appears in the other
• In other words, the two cells share the same underlying data

More generally:

• Would like to be able to link arbitrary numbers of cells that could span different sheets, such that updating any one of them updates all of them instantaneously

My use case does not require entering formulas, the user will only ever enter need to fixed values. But if such a feature did exist and did allow for formulas, I'd expect formulas to be properly relative. I.e., if you enter "=A3+1" in A4, the formula appears as "=B5+1" in B6.

Not interested in (and will not accept) any workarounds that involve setting up something like separate input/output cells, or if user has to do anything other than enter a value into a cell in a normal way. A macro solution is undesirable, but may be acceptable.

Googled, found nothing relevant. (It's hard because I'm being swamped in results on how to do string concatenation.)

Answer 1

A Worksheet Change: Populate With Changed Value

• Left: A3 was changed to 1.
• Right: C7 was changed to 2.

• There is nothing to run i.e. this code runs automatically, on each change in the given range.
• Best close all workbooks and add a new one. Right-click on the tab of the desired sheet and select View Code. Copy the following code to the window that opened in the Visual Basic Editor. Close the Visual Basic Editor and start testing the code. If you're satisfied with the functionality, save the workbook as a .xlsm or .xlsb file. Apply the same in your desired workbook.

Sheet Module, e.g. Sheet1

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    PopulateWithChangedValue Target, "A3,B5,C7,D9"
End Sub

Sub PopulateWithChangedValue( _
        ByVal Target As Range, _
        ByVal RangeAddress As String)
    Const PROC_TITLE As String = "Populate With Same Value"
    On Error GoTo ClearError
        
    Dim srg As Range: Set srg = Target.Worksheet.Range(RangeAddress)
    Dim trg As Range: Set trg = Intersect(srg, Target)
    If trg Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    srg.Value = trg.Cells(1).Value

ProcExit:
    On Error Resume Next
        Application.EnableEvents = True
    On Error GoTo 0
    Exit Sub
ClearError:
    MsgBox "Run-time error '" & Err.Number & "':" & vbLf & vbLf _
        & Err.Description, vbCritical, PROC_TITLE
    Resume ProcExit
End Sub