Cell is equal to another cell until you write something in it

[Paul_Hossler]

I added a field that is paired with fields on both Sheet2 and Sheet3

The aryPair() rows has to be dimensioned to hold the pairs (was 6 not it's 8)

It holds Ranges so aryPair (7,1) and aryPair (7,2) have to be Set,

 
Set aryPair(7, 1) = ws1.Range("F10")
Set aryPair(7, 2) = ws2.Range("E12")

The pairs can be Set in any order in the aryPair array


Capture.JPG


It's necessary somehow to 'link' pairs of cells between sheets so that they can be updated

The Workbook_SheetChange event uses aryPair() pairs and the sheet that was changed (Sheet1 or Sheet2/Sheet3)) to see if something needs to be updated


I can do some code polishing that might make it a little more straight forward if you want
If you do, then please attach a SMALL realistic sample of real data

Option Explicit


Public aryPair(1 To 8, 1 To 2) As Range     '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<


Public ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Public rName As Range, rTown As Range, rOrder As Range, rPart As Range


'called in Thisworkbook Open
Sub Init()
    
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")
    Set ws3 = Worksheets("Sheet3")
    
    Set rName = ws1.Range("D3")     '   name
    Set rTown = ws1.Range("H3")     '   town
    Set rOrder = ws1.Range("D6")    '   order
    Set rPart = ws1.Range("H6")     '   part
    
    
    '------------------------------------------------------------ Sheet1 to Sheet2 / Sheet2 to Sheet1
    Set aryPair(1, 1) = rName
    Set aryPair(1, 2) = ws2.Range("E2")


    Set aryPair(2, 1) = rTown
    Set aryPair(2, 2) = ws2.Range("E5")


    Set aryPair(3, 1) = rOrder
    Set aryPair(3, 2) = ws2.Range("I2")
    
    Set aryPair(7, 1) = ws1.Range("F10")        '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    Set aryPair(7, 2) = ws2.Range("E12")        '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
       
    
    
    '------------------------------------------------------------ Sheet1 to Sheet3 / Sheet3 to Sheet1
    Set aryPair(4, 1) = rPart
    Set aryPair(4, 2) = ws3.Range("F2")


    Set aryPair(5, 1) = rTown
    Set aryPair(5, 2) = ws3.Range("F5")


    Set aryPair(6, 1) = rOrder
    Set aryPair(6, 2) = ws3.Range("F7")


    Set aryPair(8, 1) = ws1.Range("F10")        '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    Set aryPair(8, 2) = ws3.Range("J11")        '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<


    Application.EnableEvents = True
    
End Sub