Sorting Linked Data Between Sheets

[Inobugs]

Is there a way to link information from an Excel spreadsheet on a source
worksheet to a second (destination) worksheet, sort the source workbook, and
even though the positioning of the cell location changes in the source book;
the same data (values) are linked to the second workbook as before the sort?
Neither using names, nor removing the string ($) sign from the linked data
formula has worked. I suspect I could use a lookup formula, but I do not want
to sort on the lookup reference value (that appears to need to be in
ascending order).
If the answer is no, I suppose I could create another sheet with the
reference data sorted in ascending order so a lookup formula works. I would
like to just link, sort the source sheet when I want, and have the second
workbook somehow retain reference to the same value as before the sort.
What's the simplest way to accomplish this? Thanks. May God's fresh blessings
be about you!
Dan

 

[ryguy7272]

Of course you can do it! This should give you some ideas:
http://www.mvps.org/dmcritchie/excel/sheets.htm

HTH,
Ryan---

 

[Inobugs]

I went through the sheet at the link
"http://www.mvps.org/dmcritchie/excel/sheets.htm" three times. While I use
Excel extensively, I do not have a sufficient understanding of the coding in
macros and Visual Basic to unsderstand which part of the reference page you
sent may help me solve my issue. If I could name cells so the names continued
to refer to the same cell data (formula or value) even after a sort, that
would work fine. Otherwise, might you be able to give me more explicit
information as to how to sort the source sheet while the linked refernces on
the destination sheet (in another workbook) refer to same cell data on the
source sheet; even though its position on the source sheet has changed?
Thanks.
Dan

 

[Ken Johnson]

I went through the sheet at the link
"http://www.mvps.org/dmcritchie/excel/sheets.htm" three times. While I use
Excel extensively, I do not have a sufficient understanding of the codingin
macros and Visual Basic to unsderstand which part of the reference page you
sent may help me solve my issue. If I could name cells so the names continued
to refer to the same cell data (formula or value) even after a sort, that
would work fine. Otherwise, might you be able to give me more explicit
information as to how to sort the source sheet while the linked refernceson
the destination sheet (in another workbook) refer to same cell data on the
source sheet; even though its position on the source sheet has changed?
Thanks.
Dan

One way is to insert a new column next to the column on the source
sheet, then add a series of sequential digits (1,2,3,4,5,6,...) down
the inserted column.
This new column acts as labels for keeping track of where source
column cells have been moved to when sorted. Also, this new column
must be sorted with the source column for its cells to keep track of
changing positions.
The formula you then use on the destination sheet can be an INDEX
MATCH combination.
For example, say the destination sheet only needs to show the values
originally in the 3rd and 5th cells in the source column (Sheet1!
A2:A9), with the inserted column of tracking digits in Sheet1!B2:B9,
then...

=INDEX(Sheet1!$A$2:$A$9,MATCH(3,Sheet1!$B$2:$B$9,0)) will return the
3rd

=INDEX(Sheet1!$A$2:$A$9,MATCH(5,Sheet1!$B$2:$B$9,0)) will return the
5th.

The problem of having to remember to include the tracking column in
all sorts could be overcome using code that automatically includes it
whenever the source cells are selected.
The following code pasted into the Source worksheet's code module
would work with the above example.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A2:A9")) Is Nothing Then
On Error GoTo ERROR_HANDLER
Application.EnableEvents = False
Union(Target, Intersect(Target, Range("A2:A9")).Offset(0, 1)).Select
Application.EnableEvents = True
End If
Exit Sub
ERROR_HANDLER: Application.EnableEvents = True
End Sub

Ken Johnson