Automatic Sorting of a group of columns.
 

Is it possible to setup an excel spreadsheet to automatically sort through
columns when data is being entered?. Or does the sort command need to be used
every time i want to sort the columns by date?

One play to try ..

Assume data is entered in row2 down

In Sheet1
------------
Date Field1 Field2
01-Nov-04 Data1 Data11
02-Oct-04 Data2 Data12
03-Oct-04 Data3 Data13
04-Nov-04 Data4 Data14
02-Nov-04 Data5 Data15
etc

Use an empty col to the right, say col K

Put in K2: =IF(A2="","",A2+ROW()/10^10)

Copy K2 down by as many rows as data
is expected in the table, say down to K1000?

In Sheet2
-------------
With the same headers in A1:C1 :Date Field1 Field2

Put in A2:

=IF(ISERROR(MATCH(SMALL(Sheet1!$K:$K,ROW(A1)),Shee t1!$K:$K,0)),"",OFFSET(She
et1!$A$1,MATCH(SMALL(Sheet1!$K:$K,ROW(A1)),Sheet1! $K:$K,0)-1,COLUMN(A1)-1))

Copy A2 across to C2, fill down by as many rows
as was done in col K in Sheet1, i.e. down to C1000

Format col A as date

Cols A to C will return the table from Sheet1
auto-sorted in ascending order by the dates in col A,
(earliest dates first)
viz. for the sample data above, it'll display as:

Date Field1 Field2
02-Oct-04 Data2 Data12
03-Oct-04 Data3 Data13
01-Nov-04 Data1 Data11
02-Nov-04 Data5 Data15
04-Nov-04 Data4 Data14

--

And if you want it auto-sorted in descending order
(i.e. latest dates on top),
just replace SMALL with LARGE in the formulas, viz.:

Put in A2 (in Sheet2):

=IF(ISERROR(MATCH(LARGE(Sheet1!$K:$K,ROW(A1)),Shee t1!$K:$K,0)),"",OFFSET(She
et1!$A$1,MATCH(LARGE(Sheet1!$K:$K,ROW(A1)),Sheet1! $K:$K,0)-1,COLUMN(A1)-1))

Copy across and down as before
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Josh Barbara" <Josh wrote in message
...
Is it possible to setup an excel spreadsheet to automatically sort through
columns when data is being entered?. Or does the sort command need to be
used
every time i want to sort the columns by date?