Hi,
I've seen a lot of posts where people want to auto sort a column when for example a cell-number is changed. A lot of people on this forum say it isn't possible without a macro.
I say otherwise.
Below is an example table (with the formulas to use inside it)
Columns A are a list with sorted Teams, which are sorted by their Score (column B).
The sorting formulas are in the columns K to T.
With this formula columns A and B will automatically sort (without any macro) the moment you change a score in column M.
A: Teams B: Scores K: Unsorted: L: Teams M: Scores N: UNIQUE KEY O: AUTO KEY P: Order Rank R: Sorted: S: Teams T: Scores =S2 (will result in Team C) =T2 (will result in 8) Team A 4 =M2+O2/1000 1 =VLOOKUP(LARGE(N2:N5;O2);N2:O5;2;FALSE) =INDEX(L2:L5;P2) =INDEX(M2:M5;P2) =S3 (will result in Team B) =T3 (will result in 7) Team B 7 =M3+O3/1000 2 =VLOOKUP(LARGE(N2:N5;O3);N2:O5;2;FALSE) =INDEX(L2:L5;P3) =INDEX(M2:M5;P3) =S4 (will result in Team A) =T4 (will result in 4) Team C 8 =M4+O4/1000 2 =VLOOKUP(LARGE(N2:N5;O4);N2:O5;2;FALSE) =INDEX(L2:L5;P4) =INDEX(M2:M5;P4) =S5 (will result in Team D) =T5 (will result in 2) Team D 2 =M5+O5/1000 2 =VLOOKUP(LARGE(N2:N5;O5);N2:O5;2;FALSE) =INDEX(L2:L5;P5) =INDEX(M2:M5;P5)
Hope this helps for all who wants to auto sort without using a VBA-script or Macro.
Source: http://www.digitaria.com/blogs/excel...o-sorting-data
Greetings,
Quuador
Bookmarks