THX VERY MUCH MAX!
I tried to put this into my wookbook I MANAGED TO GET THE FIRST FORMULA TO
WORK,BUT THE SECOND IS CONFUSING ME.

IN MY WOOKBOOK I HAVE 8 SHEETS TOTAL . 7 are sheets with dates and one is to
be the report sheet.

on the sheets the dates are entered on column J, and I have the formula on
column P. (THIS FORMULA IS WORKING FINE).
one the reports sheet it is formatted the same as the other sheets. I tried
to insert the formula with cell/ # changes but it wouldnt work and im
confused with the -1's in the formula.
Also Im unsure if it would be different if I have 7 sheets which I want to
pull from. Also I was trying to have the the info from cells A THROUGH O
COPIED ONTO THE REPORTS PAGE.
I KNOWN THIS QUITE A CHALLENAGE, BUT ANY HELP OR ADVICE WOULD BE HELPFUL.

THANKS-AGAIN.

"Max" wrote:

"Markitos" wrote
.. was wondering if their is a way to transfer the
date to another worksheet within a workbox
if the exp date is expired. Also would it be possible
to make it so the their will be no spaces left blank
(condense to fill in top-bottem) ..

Give this a play ..

Assume you have in Sheet1, cols A to C
data from row2 down

Date Field1 Field2
31-Oct-04 Data1 Data2
01-Nov-04 Data1 Data2
02-Nov-04 Data1 Data2
03-Nov-04 Data1 Data2
04-Nov-04 Data1 Data2
05-Nov-04 Data1 Data2
06-Nov-04 Data1 Data2
etc

Using an empty col to the right of the data, say, col E

Put in E2: =IF(A2="","",IF(A2<TODAY(),ROW(),""))
Copy down as many rows as data is expected in cols A to C

In a new Sheet2
----------------------
With the same col headers in A1:C1, viz:
Date Field1 Field2

Put in A2:

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

Copy A2 across to C2, fill down by as many rows
as was done in col E of Sheet1

Format col A as dates

Sheet2 will extract only* the rows from Sheet1
where the dates in col A have since expired,
i.e. < TODAY() [as per formula in col E of Sheet1]
*and without any blank rows in-between

For the sample data in Sheet1,
it'll show** in Sheet2 as:

Date Field1 Field2
31-Oct-04 Data1 Data2
01-Nov-04 Data1 Data2

**"Today" is : 2-Nov-2004 over here
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <atyahoo<dotcom
----