generate list of non-entries within dates

  • Thread starter Thread starter david_g
  • Start date Start date
D

david_g

I use Excel (2007) to record activity and support for about 100
current online learners and I have to provide an activity summary
sheet every two weeks. The list of learners is in one sheet and are
marked "current", agreed break" or "completed" in an adjacent column.
The total list is about 1000 and grows by about 10 each week. The
activity log is another sheet of the same workbook. Assuming that all
activity is logged in this way I need to generate a list (sheet) of
those current learners who have not submitted any recent work and are
therefore "inactive" within the two week period. This will help me
focus my attention on them and might save mountains of paper or me
tearing my few remaining hairs least I miss one of my flock.
 
My names are in column A of Sheet1, column B has "current", "agreed break"
or "completed"
On Sheet2, column A has names while column B has the date of the last work
submitted by the learner - if you have a different lay out we can still find
the date of the latest item.

Back to Sheet1: column E is headed "Last" , column D is headed "Inactive"
in E2 the formula
=VLOOKUP(A2,Sheet2!$A$2:$B$2000,2,FALSE) (copied down the column) is sued to
bring in the date of the last work. In D2 (copied down the column) we use
=IF(AND(B2="current",TODAY()-E2>14),"X","")

Select all the data on Sheet1 and use Data | Filter
Using the drop-down arrow in the Inactive header, display only records with
an X
Select The displayed names (these are the delinquent 'current' folk), copy
and paste to another worksheet or to a Word document as required.

Hope this gives you some ideas to work on
best wishes
 
My names are in column A of Sheet1, column B has  "current", "agreed break"
or "completed"
On Sheet2, column A has names while column B has the date of the last work
submitted by the learner - if you have a different lay out we can still find
the date of the latest item.

Back to Sheet1: column E is headed "Last" , column D is headed "Inactive"
 in E2 the formula
=VLOOKUP(A2,Sheet2!$A$2:$B$2000,2,FALSE) (copied down the column) is sued to
bring in the date of the last work.  In D2 (copied down the column) we use
=IF(AND(B2="current",TODAY()-E2>14),"X","")

Select all the data on Sheet1 and use Data | Filter
Using the drop-down arrow in the Inactive header, display only records with
an X
Select The displayed names (these are the delinquent 'current' folk), copy
and paste to another worksheet or to a Word document as required.

Hope this gives you some ideas to work on
best wishes
Click to expand...

Thanks to both of you. Bernard's solution has an elegant simplicity
that I can understand and adapt but the idea of a macro that runs at
the touch of a button seems cool.
Thanks again
 
Back
Top