Creating link between different columns

[bulletcss]

Hi there.

I have a problem I need to tackle.

There is a table that consists of three columns. Two highlighted are loaded from a source file. The third column is entered by user manually.
But sometimes the arrangement of rows in the source file is changed. So after that, the third column corresponds to the data wrongly.

I tried to illustrate that. It can be seen that the pairing for "Elem4" and "Elem6" are broken after shuffling on the right table.

So, my question is how to "link" the values in columns 1 and 2 to values from column 3 to make them "move together"?

The only solution I found is creating an extra sheet to store data pairs there and parse third column values with INDEX and MATCH.
Is there a more elegant solution to this problem?

 

[bulletcss]

in fact, i was storing info that was not needed.

Private Sub Workbook_Open()

'this assumes column 1 is column b, 2 is column c,3 is column d, change to suit your data

Application.Calculation = xlManual
    lr = Range("A" & Rows.Count).End(xlUp).Row
    ReDim B(1 To lr) As String
    ReDim D(1 To lr) As String
   
    'store the options
    For RW = 2 To lr
        If Range("D" & RW) <> "" Then
            B(RW) = Range("B" & RW)
            D(RW) = Range("D" & RW)
        End If
    Next RW
   
    Application.Calculation = xlAutomatic
    Application.Calculate
   
    're-apply the options
    Range("D2:D" & lr).ClearContents
    For RWw = 2 To lr
        For RW = 1 To lr
            If B(RW) = Range("B" & RWw) Then
                Range("D" & RWw) = D(RW)
            End If
        Next RW
    Next RWw


End Sub

I really appreciate your enthusiasm. Thanks a lot!

VBA events are looked for me as a right direction.
Open_workbook event doesn't fit me because links can be updated after opening, so it will brake the integrity of data. And there is no OnLinkUpdate event.

I will try to work with on_change event combined with saving date on the hidden sheet.

 

[SQUIDD]

Well, yes i would think you could just apply the code to a sheet change event, but link it to a particular range