Re Formula linking sheets

gelder

New Member
Joined
Jan 20, 2005
Messages
8
This may be an easily solved question, but here goes.

I have my main list in sheet 1, with a sub-list on sheet2. I would like to have sheet 2 change automatically as I rearrange sheet 1.

Ex. Original is Sheet 1, Cell A5. Sheet 2, A1 is linked to this.
When I alphabetize Sheet 1, A5 moves to A7.
Sheet 2 A1 is still linked to Sheet 1 A5. I need it to be now linked to Sheet 1 A7.

This is the formula I have used: =Sheet1!A5. I would like it to automaticall change to: =Sheet1!A7 (as necessary). But this does not change as I change sheet 1. Please help! :rolleyes:
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

scifibum

Well-known Member
Joined
Jul 22, 2004
Messages
503
I don’t think this is easily solved. If your data was being moved around by cutting and pasting, then I think the linked cell would “keep up.” But Excel doesn’t seem to track the movement of cell values/formats when you use sorting.

It’s as if Excel leaves cells where they are, and simply re-enters the values and certain formatting (the “Interior” formats I think) when you sort.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, gelder,

WELCOME to the BOARD !!
this problem seems never to have been solved on this Board. So I give you a "bump" (coming again on top of the threadlist). Searching on the Board, you can find about the same question, but never a final answer.

HELLO, you real PROFS here, IS THERE AN ANSWER to this question?

sorting cells is in fact permutating the values within those cells and NOT permutating the cells themselves
you would need another way to sort the cells, so the link could "follow" the movements of the cells

here are some of the searchresults (after about an hour of surfing)
http://www.mrexcel.com/board2/viewtopic.php?t=63249&highlight=sorting+cell+links
http://www.mrexcel.com/board2/viewtopic.php?t=64576&highlight=sorting+links+error
http://www.mrexcel.com/board2/viewtopic.php?t=109165&highlight=formula+linking+sheets
but is there an answer ?

let's hope this BUMP will help you!
Erik

EDIT: you already had your bump :p
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Does this help?
Sorting the items in column C (only!) ==> the items in column E will follow the movements.
kind regards,
Erik
Map2.xls
ABCDE
1sheet1!C111
2sheet1!C244
3sheet1!C366
4sheet1!C455
5sheet1!C533
6sheet1!C622
7sheet1!C777
sheet1
 

scifibum

Well-known Member
Joined
Jul 22, 2004
Messages
503

ADVERTISEMENT

OK, I'm no "prof" but I'm going to work on this one. I bet there's a way to accomplish this by copying the cell contents somewhere else along with the original cell address in an adjacent column, sorting that temporary list, and then comparing that sorted list against the original list and cutting/inserting things from the original list to match the sorted list order.

It would definitely be a "workaround" solution...I don't think there's any way to get the existing sort function to do this, you'd have to use a custom sort macro instead.

I'll post back later.
 

chiello

Well-known Member
Joined
Jan 18, 2005
Messages
848
gelder said:
Ex. Original is Sheet 1, Cell A5. Sheet 2, A1 is linked to this.
A possible solution is the following:

add the same label to linked cells of sheet1 and sheet2 in adiacent column. For example, add label "5" in cell B5 of sheet1 and in cell B1 of sheet2. Then type the following formula in cell A1 of Sheet 2:

=INDEX(Sheet1!$A$1:$A$5;MATCH(B1;Sheet1!$B$1:$B$5;0))

I supposed you have original data in range A1:A5 and relevant labels in range B1:B5

Include labels column in your sort operation and the all shoud work

Ciao
 

scifibum

Well-known Member
Joined
Jul 22, 2004
Messages
503

ADVERTISEMENT

I came up with something that works OK. I'm sure this isn't as pretty as it could be, but for me it works.

This allows you to "sort" a range by automatically cutting and rearranging the cells themselves instead of using Excel's sorting tool. It doesn't require keeping extra labels or using INDIRECT formulas (although those are perfectly valid methods).

Because the cells are moving around using cut/insert, any linked cells still point to the cell after it's moved to a new place.

Hopefully the comments in the code explain this sufficiently.

I may come back to this later and try to dress it up with some options, such as allowing multiple-key sorting, allowing sorting when there is no header row, etc. (If someone else doesn't beat me to it. :))

Here's the macro:

Code:
Option Base 1
Sub SortEntireCells()
'This macro takes a selected range and "sorts" it by
'cutting and inserting rows, so that links to the actual
'cells will "follow" the cells after they are sorted.
'
'In order to use this macro, select a data range and run this.
'The selected data range should contain headers/field names.
'The sort "key" will be the header in the column of the active cell.
'The range to be sorted can be one or several columns.    
    
    Application.ScreenUpdating = False
    
    Dim rngSort As Range
    Dim r As Integer
    Dim rCount As Integer, cCount As Integer
    Dim keyOffset As Integer
    Dim OriginalRows() As Integer
    Dim wbSort As Workbook
    
    Set wbSort = ActiveWorkbook
    Set rngSort = Selection
    keyOffset = ActiveCell.Column - Selection.Column
    
    rCount = rngSort.Rows.Count
    cCount = rngSort.Columns.Count
    
    'We this array to store row numbers
    ReDim OriginalRows(rCount) As Integer
        
    'Copy the range to be sorted into another workbook
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    
    'Storing the original position of each row in the range
    With Cells(1, cCount + 1)
        .Value = "Original Row"
        .Font.Bold = True
        .Font.Italic = True
    End With
        
    For r = 2 To rCount
        Cells(r, cCount + 1).Value = r
    Next r
    
    'Select entire copied range and the added row numbers, then sort
    ActiveSheet.UsedRange.Select
    Selection.Sort Key1:=Cells(1, 1).Offset(0, keyOffset), Header:=xlYes
                        
    'Store the original row numbers in an array in the new sorted order
    For r = 2 To rCount
        OriginalRows(r) = Cells(r, cCount + 1).Value
    Next r
    
    'Close the temporary workbook
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True
    
    'Back to the original workbook
    wbSort.Activate
    
    'Creating a temporary name for each row in original range,
    'the "number" of each name corresponds to the position in the
    'sorted range
    
    For r = 2 To rCount
        
        wbSort.Names.Add Name:="temprow" & r, _
            RefersTo:=Range(Cells(rngSort.Row + OriginalRows(r) - 1, rngSort.Column), _
            Cells(rngSort.Row + OriginalRows(r) - 1, rngSort.Column + cCount - 1))
        
    Next r
            
    'Cut and insert rows to their new order.
    For r = 2 To rCount
        If Range("temprow" & r).Row <> rngSort.Cells(r, 1).Row Then 'Skip row if it's already in the right place
            Range("temprow" & r).Cut
            rngSort.Cells(r, 1).Insert Shift:=xlDown
        End If
    Next r
    
    'Delete the temporary row names
    For Each nm In wbSort.Names
        If Left(nm.Name, 7) = "temprow" Then nm.Delete
    Next nm
    Application.ScreenUpdating = True
End Sub
 

scifibum

Well-known Member
Joined
Jul 22, 2004
Messages
503
Note: this runs pretty slow on long lists. On a 500 row list it took about 10-15 sec for me, on a 2.2ghz dell workstation
 

scifibum

Well-known Member
Joined
Jul 22, 2004
Messages
503
17 minutes! on a 5000 row list. So I wouldn't recommend this for anything over a few hundred maybe.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
scifibum,

good try :p

I was just about to ask you if it wouldn't take to long to run...
Perhaps you can try a trick,
inserting a column, adding formulas ?
some operations which seem at first only to be possible one by one, could perhaps be done "at once"

or doing calculations "outside" the sheet by using "computed arrays" like here
this must be the trick !
I'm no "pro", also just learning this...

kind regards,
Erik

Code:
Option Explicit

Sub array_in_memory_trick()
    Dim mem As Variant
    Dim i As Long

    'Get all values as an array
    mem = Range("A1:A10000").Value

    For i = LBound(mem, 1) To UBound(mem, 1)
    mem(i, 1) = mem(i, 1) & " scifibum"
    Next i

    'Put array back in place
     Range("A1:A10000").Value = mem

End Sub
 

Forum statistics

Threads
1,147,621
Messages
5,742,188
Members
423,710
Latest member
Duarte85

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top