extract name when a date in another cell gets near

[Guest]

Hi please help
I have a database of names in Column A and columns for assessments.
So A1 - 2 has a name. B1 has a due date in. And there is a space for a date
to be entered in B2 for when it was completed. This cell (b2) has 3
conditional formats to change the cell colour to yellow if a date has been
entered, goes purple if a date is not entered and is 45 days to due date and
red if there are 15 days to go. What i want to do is to extract a name to
another worksheet when there has not been a date entered and is 45 days till
the due date and also put the type of assessment

Please help its driving me bonkers!!!!!!!!!

 

[Max]

Not sure of your set-up, but here's one interp / way ..

Sample construct at:
http://cjoint.com/?lijhId05fe
Extracting_Lines_Date_Criteria_cityfc_wks.xls

In Sheet1
----------
Assume the set-up in cols A to E is:

Due date: 25-Dec-05
Name1 11-Nov-05 N1Assmt1 N1Assmt2 N1Assmt3
Name2 (blank) N2Assmt1 N2Assmt2 N2Assmt3
Name3 09-Nov-05 N3Assmt1 N3Assmt2 N3Assmt3
Name4 12-Nov-05 N4Assmt1 N4Assmt2 N4Assmt3
Name5 (blank) N5Assmt1 N5Assmt2 N5Assmt3
etc

Dates are in col B, with the due date entered in B1

Put in G2:
=IF(OR(B$1="",A2=""),"",IF(AND(B2="",B$1-TODAY()<45),ROW(),IF(B$1-B2<45,ROW(
),"")))

Copy G2 down to say G10
(cover max expected extent of data in col A)
Leave G1 empty

In Sheet2
----------
Put in A2:
=IF(ISERROR(SMALL(Sheet1!$G:$G,ROWS($A$1:A1))),"",
INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!$G:$G,ROWS($A$1:A1)),Sheet1!$G:$G,0)))

Copy A2 across to E2, fill down to E10
(cover the same extent as done in col G in Sheet1)
Format col B as date

Sheet2 will return the desired results neatly bunched at the top

 

[Guest]

Thanks Max for your help but not quite what i had in mind, i think its the
way i tried to interpret it i will try again:-

this is how it looks


A B C D E F
G H
1 Surname First Formal Driving Ass OTDR
Interim
2 Earliest Latest Earliest Latest Earliest Latest
3 Anyone A 18/05/05 17/11/05 18/05/05 17/11/05 18/11/05 17/05/06
4 18/06/05 20/01/06
etc with other names

Now a date is placed in row 4 when the assessment was completed, as
you can see under OTDR there is no completion date entered and the 17th
November is getting close (within 45 days of latest date) so what i would
like is on a different sheet the name and the assessment (in this case otdr)
to come up but there could be more than 1 assessment needed but it would be
ok if name appears twice in list with appropriate assessment. Then i could
print list off so managers know who needs to be assessed.

Now this next bit could be complicated, in addition to the above on the left
of the list i would like names and ass within 45 days to be completed and on
right, names and ass within 7 days to be completed under heading ASAP but not
appearing on left side for same assessment.

Hope this explains it better than b4 and hope you will help me again

 

[Max]

cityfc,

Gave it all I've got and more for the past 2 solid hours, believe me ..
Received your file and studied your sheet layout, but despite best efforts,
regret I'm unable to offer you any further suggestion.

 

[Guest]

thanks for trying and giving it your all. i will try it in programming

thanks again cityfc

 

[Max]

You're welcome !
Good luck ..