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
ate Field1 Field2
Put in A2:
=IF(ISERROR(MATCH(SMALL(Sheet1!$K:$K,ROW(A1)),Sheet1!$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)),Sheet1!$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
