how to sort cells with sheet reference

  • Thread starter Thread starter vba_neophyte
  • Start date Start date
V

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
 
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:
Click to expand...
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)
 
Back
Top