Sort automatically

[Phil]

Hi,
Is it possible to automatically sort inputted data when the 'enter'
key is pressed?

1 2 3 4
5

A: 2 2 0 0 4
B: 1 0 0 0 1
C: 1 0 0 0 1
D: 1 0 0 0 1
E: 1 0 0 0 1
F: 0 0 0 0 0

For instance, if any cell C1:C4 was to increase by 2 then C5 would
equal 3 and therefore move up and replace row B. I can do it manually
using sort but was wondering if there is a "lazy" way.

Thanks all

 

[Nick Hodge]

Phil

There's not an easy way, but you could put some code similar to that below
behind the worksheet you want to sort. (To do this right click on the sheet
tab and select 'view code...'. Paste the code here)

The code as written will detect a change in the worksheet, decide if it is
in the range A2:E7. (If not it does nothing). It then temporarily disables
XL from carry out other events. (Or else the sort will trigger a new event
and so on). Sorts the range based on column E and then re-enables the events

Give it a try

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A2:E7")) Is Nothing Then
Application.EnableEvents = False
Range("A1:E7").Sort Key1:=Range("E2"), _
Order1:=xlDescending, Header:=xlGuess
Application.EnableEvents = True
End If
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Max]

Perhaps this set-up ..

Assume you have

In Sheet1
-------------
In cols A to E, row1 down, the table below
where col E is the sum of the numbers in cols A to D,
viz. in E1 is : =SUM(A11), E1 copied down

2 2 0 0 4
1 0 0 0 1
1 0 0 0 1
1 0 0 0 1
1 0 0 0 1
etc

Put in F1: =IF(E1=0,"",E1-ROW()/10^10)

Copy F1 down as many rows as there is data
or copy down to a max expected number of rows
in which data is likely to be input in cols A to D

Col F will function as an arbitrary tie-breaker
to enable complete extract of all rows in Sheet2
in the event of ties (ties would surely happen,
going by your sample data)

In another Sheet2
--------------------------
Put in A1:

=IF(ISERROR(MATCH(LARGE(Sheet1!$F:$F,ROW()),Sheet1!$F:$F,0)),"",OFFSET(Sheet
1!$A$1,MATCH(LARGE(Sheet1!$F:$F,ROW()),Sheet1!$F:$F,0)-1,COLUMN()-1))

Copy A1 across to E1, then copy down by as many rows
as you have copied the formula in col F of Sheet1

Cols A to E in Sheet2 will return the
desired automatic descending sort of cols A to E in Sheet1
(sorted by the summed values in col E in Sheet1)

 

[Ragdyer]

I don't know if this is what you're looking for, exactly.

Here's a formula that will "sort" a helper column, as numbers are keyed into
a "main" column.
For example, enter this in B1, and copy down to B20.
Then enter random numbers into columnA, and see what happens.

=SMALL($A$1:$A$20,ROW())

This can be adapted to columns, using the same principal:

=SMALL($A$1:$X$1,COLUMN())