A cell in Worksheet 1 contains (by reference) the content of a particular
cell in Worksheet 2. If the cell-ID for the referenced content changes(say
due to a sort) how do I maintain the link to the content?
If 1CellE32 = 2CellX75 before the sort
And the sort moves 2CellX75 to 2CellX25
Then I want 1CellE32 = 2CellX25 after the sort
Can you help please?
One way is to insert a helper column next to column X on sheet 2. This
helper column can hold labels that excel can then use to keep track of
where a column X value has moved to after a sort. So, if the inserted
column is column Y it can hold the values 1,2,3,4... n+1 down the
column as far as required. If this numbering starts with 1 in Y1 then
the value in X75 will have 75 next to it in column Y.
On sheet 1, E32, the formula used to refer to the value in sheet 2 X75
is...
=INDEX(Sheet2!X:X,MATCH(75,Sheet2!Y:Y,0))
or, if you are wanting to fill the formula to other column E cells...
=INDEX(Sheet2!X:X,MATCH(ROWS($1:75),Sheet2!Y:Y,0))
Now, when sheet 2 is sorted, provided column Y is included in the
sort, the column Y and column X values move together and the above
formula ensures that the value in sheet 1 E32 is not affected by the
sort.
An obvious problem is how to ensure that the column Y values are also
selected before column X is sorted. The following
Worksheet_SelectionChange event procedure should solve that problem...
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("X:X")) Is Nothing Then
If Intersect(Target, Range("Y:Y")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
Union(Target, Intersect(Target, _
Range("X:X")).Offset(0, 1)).Select
Application.EnableEvents = True
End If
End If
End Sub
This code needs to be pasted into the Sheet 2 code module. Copy the
code then right click the Sheet2 tab and select View Code from the
popup menu. Paste the code then press Alt+F11 to get back to the user
interface.
Ken Johnson
