I am using Excel (Excel 2003 on Windows XP) to track due dates that are
based on a certain event date (e.g. 30days, 100days, 120days, and 180days
from the event date) and had no problem determining each due date by formula.
The due dates appear in columns N,O,P,Q, the event date is in column B.
Columns C to M contain text and numbers (names, dates of birth, attained age,
a prior event in date format, file numbers, etc.).
Each row represents a new record for a different name with either the same
or a different event date (which means that two rows may have the same due
dates). Here's what I would like to achieve: for Excel to remove each row
from the worksheet once the due date for the last report in that row has
passed at the 181 day mark. Also, if at all possible, I would like for
Excel to move all data contained in that row to a new worksheet where the
data will be tracked further with 1year and 2year endpoints. Hopefully,
someone out there understands my layman's language.
Thanks in advance for responding!
--
wmc

Try this formulas play which delivers the desired equivalent results

Source data assumed entered in sheet: X, in cols A to Q from row2 down,
with the key col = col Q which contain the "180days" dates (real dates)

In a new sheet named as say: E
(E=Expired cases)

Put in A2:
=IF(X!Q2="","",IF(TODAY()X!Q2,ROW(),""))
Leave A1 blank

Put in B2:
=IF(ROW(A1)COUNT($A:$A),"",INDEX(X!A:A,SMALL($A:$ A,ROW(A1))))
Copy B2 to R2. Select A2:R2, copy down to cover the max expected extent of
data in X, say down to N100. Format cols C, O to R as dates, etc. Cols B to R
returns the lines from X which have expired (dates 180 days), with all
lines neatly bunched at the top.

Then just make a copy of E, name it as say: C
(C=Current)

In E,

Replace the formula in A2 with:
=IF(X!Q2="","",IF(TODAY()<=X!Q2,ROW(),""))
Then copy A2 down to A100. Leave the rest unchanged. Cols B to R returns the
lines from X which are still current (ie dates <= 180 days), with all lines
neatly bunched at the top.

Lines in X will hence dynamically transfer to E or C
depending on the dates in col Q, as desired.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"wmc" wrote:
I am using Excel (Excel 2003 on Windows XP) to track due dates that are
based on a certain event date (e.g. 30days, 100days, 120days, and 180days
from the event date) and had no problem determining each due date by formula.
The due dates appear in columns N,O,P,Q, the event date is in column B.
Columns C to M contain text and numbers (names, dates of birth, attained age,
a prior event in date format, file numbers, etc.).
Each row represents a new record for a different name with either the same
or a different event date (which means that two rows may have the same due
dates). Here's what I would like to achieve: for Excel to remove each row
from the worksheet once the due date for the last report in that row has
passed at the 181 day mark. Also, if at all possible, I would like for
Excel to move all data contained in that row to a new worksheet where the
data will be tracked further with 1year and 2year endpoints. Hopefully,
someone out there understands my layman's language.
Thanks in advance for responding!
--
wmc

Typo ..

In E,

should have read as:

In C,

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thanks, Max.

Your instructions have been extremely helpful
--
wmc


"wmc" wrote:

I am using Excel (Excel 2003 on Windows XP) to track due dates that are
based on a certain event date (e.g. 30days, 100days, 120days, and 180days
from the event date) and had no problem determining each due date by formula.
The due dates appear in columns N,O,P,Q, the event date is in column B.
Columns C to M contain text and numbers (names, dates of birth, attained age,
a prior event in date format, file numbers, etc.).
Each row represents a new record for a different name with either the same
or a different event date (which means that two rows may have the same due
dates). Here's what I would like to achieve: for Excel to remove each row
from the worksheet once the due date for the last report in that row has
passed at the 181 day mark. Also, if at all possible, I would like for
Excel to move all data contained in that row to a new worksheet where the
data will be tracked further with 1year and 2year endpoints. Hopefully,
someone out there understands my layman's language.
Thanks in advance for responding!
--
wmc

Pleased to hear that.
You're welcome.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"wmc" wrote in message
...
Thanks, Max.

Your instructions have been extremely helpful
--
wmc