Maintaining cell reference after sorting

[Guest]

Is there a way to maintain a cell reference after sorting a table? I want to
identify the row number of a specific cell after sorting. I have tried using
"Insert>Name" with both absolute and relative addresses. Neither one gives
me what I need. I am literate in VB if I need to write a macro.

Thanks in advance.

 

[R.VENKATARAMAN]

perhaps I have not understood the question. you name a range by siimply
highlight the range and click the arrow to the left of the sign <=> in the
formula bar and type a name and hit <enter>. the range is named. if you
sort the range, the name remains for the range
If this is not what you want apologise.

 

[Guest]

If I name a cell in this manner the "Refers to:" under "Insert>Name>Define"
is "=Sheet1!$A$17". When I sort, the reference remains this, but the cell I
am interested in is now in a different spot on the sheet , for instance
"A29". If I change the "Referes to:" a relative reference "=Sheet1!A17" I get
the same behavior.

 

[R.VENKATARAMAN]

as far as I know(I am not an expert) name is only with reference to a cell
address and not cell value. What you want needs a macro. I have given a
code. your data is in A6 to A24 no headers. and your target cell is A17

try this code on an extract of the data. in this code there is no naming.
may not be elegant but if it satisfied your need it is ok.
code begins
Option Explicit


Public Sub test()
Range("a17").Activate
Dim myvalue
myvalue = ActiveCell.Value
MsgBox myvalue
'the following code sorts therange with key column as A
'no headers-ascending order

Range("a6:a24").Sort key1:=Worksheets("Sheet1").Range("A1")
Cells.Find(what:=myvalue).Activate
MsgBox ActiveCell.Address


End Sub
code ends
the last messagebox gives the new cell address .
does this satsify your requirements
If there are duplicate values only the first cell address after sorting
will be given.

 

[Guest]

Hi
you can't. A link is referring to the cell and not to the content. So
sorting does NOT change the reference. Maybe you could add a unique ID to
each row/record and use VLOOKUP to get the correct record/value

 

[Guest]

Hi Deets LaMoss

I'm having similiar difficulty. Relative cell reference act like an absolute
reference. When the precedent worksheet is sorted the decendant worksheet
reflects the cell reference prior to the sort. My experience has been that by
using relative reference formats =A1 the cell reference would change with a
sort in the precedent worksheet, always giving the same result in the
decendant worksheet. Using absolute reference would force the data to always
reflect what was in the cell referenced. If 10 was in cell A1 and a sort
moved it to A99, the number 10 would still be reported in the linked cell.
Using absolute reference =$A$1, the sort would report whatever data was moved
into the =$A$1 cell. Never had this happen before and have yet to resolve.
i'm wondering if there is a global setting that lock the spreedsheet or if
various format setting cause this problem.

 

[Frank Kabel]

Hi
in this case it does not matter whether you use a relative or absolute
reference. Sorting will not change the reference in either case.