Auto Sort

[LiAD]

Hi,

If I have a list of text and data is it possible for excel to sort by
decreasing automatically without the user having to do it each time? Example

from this to
Bill 1 Jane 5
Fred 2 Ted 3
Jane 5 Fred 2
Ted 3 Bill 1
Gem 1 Gem 1

Hence if i then change Jane to 0 excel will automatically put her in last
place. I want to use the second chart to plot a graph which will also update
automatically with the new order.

Anyone know?

Thanks

 

[Shane Devenshire]

Hi,

Assume the data is in A1:A100 which I will name M here. Then in B1 enter
and copy down the following array formula:

=IF(COUNTA(M)<ROW(C1),"",INDEX(M,MATCH(ROW(C1),COUNTIF(M,"<="&M),0)))

This formula assume you have solid data, meaning no blanks before the last
entry. However there may be blanks in the range, but they must be below the
data. You enter an array formula by pressing Shift+Ctrl+Enter instead of
Enter.