how to sort cells with sheet reference

[vba_neophyte]

i an unable to sort the data in cells containing a reference to anothe
sheet. it seems as though excel is treating this reference as absolut
and not relative. is there any way to do this, such that thes
equations adjust to the sorting? any thoughts would be appreciated.
thanks

 

[Ninja Stevie]

one way would be to copy and paste specail values only - although this
would require updating and refreshing each time.

Another way would be to make use of the rank function. you could then
list the results of this using index!

eg.

Readout from excel:

data: number Rank Position Index
123 2 1 43
43 1 2 123
1234 3 3 1234

Formulae:

number Rank Position Index
123 =RANK(A2,$A$2:$A$4,1) 1 =INDEX($A$2:$A$4,B2,1,1)
43 =RANK(A3,$A$2:$A$4,1) 2 =INDEX($A$2:$A$4,B3,1,1)
1234 =RANK(A4,$A$2:$A$4,1) 3 =INDEX($A$2:$A$4,B4,1,1)