generate list of non-entries within dates

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.
0
david_g
3/3/2010 1:23:31 PM
excel 39879 articles. 2 followers. Follow

3 Replies
651 Views

Similar Articles

[PageSpeed] 41

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
-- 
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"david_g" <david@gts.co.uk> wrote in message 
news:1e95d68f-b1e4-40d1-b7ce-b541267c3382@q23g2000yqd.googlegroups.com...
> 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. 

0
Bernard
3/3/2010 3:15:44 PM
Excel 2007 PivotTable
Value Filter.
Optional macro.
http://www.mediafire.com/file/mt5yw1jinkn/03_03_10.xlsm

0
Herbert
3/3/2010 7:03:02 PM
On Mar 3, 11:15=A0am, "Bernard Liengme" <blien...@TRUENORTH.stfx.ca>
wrote:
> My names are in column A of Sheet1, column B has =A0"current", "agreed br=
eak"
> or "completed"
> On Sheet2, column A has names while column B has the date of the last wor=
k
> submitted by the learner - if you have a different lay out we can still f=
ind
> the date of the latest item.
>
> Back to Sheet1: column E is headed "Last" , column D is headed "Inactive"
> =A0in E2 the formula
> =3DVLOOKUP(A2,Sheet2!$A$2:$B$2000,2,FALSE) (copied down the column) is su=
ed to
> bring in the date of the last work. =A0In D2 (copied down the column) we =
use
> =3DIF(AND(B2=3D"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 wi=
th
> an X
> Select The displayed names (these are the delinquent 'current' folk), cop=
y
> and paste to another worksheet or to a Word document as required.
>
> Hope this gives you some ideas to work on
> best wishes
> --
> Bernard Liengme
> Microsoft Excel MVPhttp://people.stfx.ca/bliengme
>
> "david_g" <da...@gts.co.uk> wrote in message
>
> news:1e95d68f-b1e4-40d1-b7ce-b541267c3382@q23g2000yqd.googlegroups.com...
>
>
>
> > 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. =A0The 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. =A0The
> > activity log is another sheet of the same workbook. =A0Assuming that al=
l
> > 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. =A0This 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.

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
0
david_g
3/4/2010 12:00:42 PM
Reply:

Similar Artilces:

Can I change the calendar year beginning date?
Our fiscal year runs July 1 thru June 30 of each year. I need to track attendance, gas cards, etc for monthly reports. I have a HUGE table with a gazillion queries and reports for each one. I need one report to reflect quarterly output for our fiscal year as stated above. Can I make this report do that? Thank you! Create a query to use as the source for your report. In query design, type this into the Field row: FinYear: DateAdd("m", -6, [InvoiceDate]) replacing InvoiceDate with the name of your date field. This yields 2007 for all dates in the 2007/2008 financial year...

Date Formatting when Concantenating
I have a simple question. I have a cell that has date that looks lik this: 10/15/1999 14:34 When I use the concantenate feature my date looks like this: 36448.6073611111 I tried to format the call every which way - but I cannot get it t look the original. Feeling really silly for even asking.. thanks all for your help -- Message posted from http://www.ExcelForum.com Hi bleu808! Use: ="Today is "&TEXT(TODAY(),"mm/dd/yyyy hh:mm") -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au "bleu808 >" <<bleu808.189yij...

Show a date 30 days out
I have a feild that I would like to show what the date would be in 30 days for a feild with a date in it [Sold Date]. Sold Date feild would be 1 Jan 07 so the feild would show 30 Days later 31 Jan 07. I did =[Sold date]+30 and it did not work Thanks how about dateadd: dateadd("d", 30, [Sold Date]) -- steve. "KAnoe" <KAnoe@discussions.microsoft.com> wrote in message news:DE9BDDAE-5C84-4A52-8185-DAB6C3167E7A@microsoft.com... >I have a feild that I would like to show what the date would be in 30 days > for a feild with a date in it [Sold Date]. > ...

How OMPM Scanner (offscan) Filter by Access/Modified Date ?
Hello, I have problem to inventory excel files on very big file server, but I believe there are so many documents we no longer need to maintain. I want to skip files if the access date or modified date longer than 6 month, but don't see the OMPM providing feature about it. I currently running OMPM since 2 weeks ago and running out of time for reporting to my manager. Please help me, this is my critical assignment. -- Eldi Munggaran ...

Dates in fomula showing as whole number
I have a fomula in a cell that takes the name of a person (from cell 2B), their License number (from another cell 2C)and the Date that License Expires (From cell 2D). The expire date in "2D" is either the word "none" or a date that that persons license needs to be renewed. Those instructors with "None" come out in the calculated field fine, however the ones with dates come back as whole numbers, Example 8/6/10 shows 40396. any help will be appreciated Hi, You need to change the format of that cell or column, highlight the cell or the column, right click o...

One Entry to Multiple Rows
I have data that looks like this: X1 | Y1 Y2 Y3 Y4 X2 | Y4 Y5 Y6 Y7 And I need to get to: X1 | Y1 X1 | Y2 X1 | Y3 X1 | Y4 X2 | Y4 ...... etc. I can change the 2nd row's entries to more columns, but that doesn't seem to get me much closer to the needed format (and there are thousands of lines so I'd rather not do it manually). Any ideas? should do it. change mc to suit '===== Option Explicit Sub lineemup() Dim mc As Long Dim mr As Long Dim i As Long Dim lc As Long mc = 3 'col c mr = 1 For i = 1 To Cells(Rows.Count, mc).End(xlUp).Row lc ...

Is it possible to generate non-technical schema validation errors?
With the 1.0 Framework, I've worked out using the XmlValidatingReader. Since I'm using the validation errors as feedback to the end user, I'm hoping to get away from techy messages such as "The 'http://tempuri.org/XMLFile1.xsd:MaxDependents' element has an invalid value according to its data type. An error occurred at file:///c:/work/prodika/main/code/apps/schemavalidation/XMLFile1.xml(8, 25)." and go with a user friendly message of "Max Dependents must be between 0 and 10". I've scoured the newsgroups, MSDN and docs for creating custom valid...

Line Chart with dates in 5 day working week only
Hi, Trying to format a chart so that only the 5 working days of the week are displayed on the x axis. The source data only has the five days (e.g. 05/09/2005 down to 09/09/2005 and then on to 12/09/2005 down to 16/09/2005 etc etc) So I have missed out the weekend dates. When I create the line chart however, the weekend dates appear automatically and just show no point on the chart, therefore there is a longer line between Fridays and Mondays!! Hope this makes sense. Does anyone have any ideas on how to change this? I have tried looking at Tools-options-chart and cannot seem to turn...

Need the ability to Moderate distribution lists in Exchange
We are moving off of a mail list server and pulling all mail lists into Exchange 2003. Currently users have the ability to moderate mail sent to some lists. (Mail is sent to the moderator first, they approve or deny it, and based on approval it's sent to the list.) How can we set this up in Exchange 2003 SP1?? Thanks! On Wed, 29 Jun 2005 10:38:03 -0700, Lee <leemack33@hotmail.com> wrote: >We are moving off of a mail list server and pulling all mail lists into >Exchange 2003. Currently users have the ability to moderate mail sent to >some lists. (Mail is sent to...

How to write a new entry in a combo box to its underlying table
Dear Access 2007 VBA Gurus, I have a assets database (rather uncreatively named "Assets"). I use a form (named "Asset Acquisition Input Form New") to enter new assets. The "Manufacturer" field (combo box name "Manufacturers_ID) on this form is a lookup to a Manufacturers table. What I want to happen is when I enter an item that is not in the lookup list, I want a message box to prompt me to add the new entry to the underlying table, or to cancel and select an item from the list. I have no trouble with the MsgBox command itself. What I don&...

Outlook 2003 Imap Auto Purge wish list
I hope someone at Microsoft can code in a auto purge Imap deleted email function into there next Service Pack. I can see a problem rolling this out to our employees. There going to delete messages and those messages are going to stay on the Email server until they do a Purge. I'm afraid they just won't do this. We use Imap. No auto purge means emails staying on the server which over time just takes up hard drive space and makes tape backups take longer. wayne Hi Wayne, You should send suggestions to mswish@microsoft.com as the newsgroups are not monitored for feature request...

How to get only the year in the date format in Access
How to get only the year in the date format I.e in the table in need to display only year E.g 2005 - should be display " 05" automatically Custom format the cell as: yy -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "yanu" <yanu@discussions.microsoft.com> wrote in message news:14CE9F60-F7B9-467A-8C16-71088C31BEBA@microsoft.com... > How to get only the year in the date form...

email address on Service Call Entry window
Add an email address field to the Service Call Entry window for the customer contact on the service call. Even if it is not pulled from the email address of the address id on the service call, at least the dispatcher could enter an email address on the service call in order to communicate with the customer regarding the call. It is common to take email address as part of contact information. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the m...

Finding characters within a text
Hi How to check, using single formula, that a text within a certain cell contains one of certain characters? For instance, how to check if there is a 'R', 'L' or 'Ps' character within cell A1 that reads 'W-RII'. Thanks in advance Hmm..not very elegant, but maybe =NOT(AND(ISERROR(FIND("R",A1)),ISERROR(FIND("L",A1)),ISERROR(FIND("P",A1)))) Returns TRUE if the text in A1 contains 'R', 'L' or 'Ps' . FIND is case-sensitive, use SEARCH if you also want to find lower-case characters. Cheers, Joerg Mochiku...

date/time formatting
I download a csv report from a web based program. Everything works great but the date comes through as "Jul 21 2009 1:51pm". I do not need the time in my report. I have tried reformatting the cells, tried to copy to a new file with the cells already formatted to "7/21/09", opening the cell format using the F2 key and deleting the time (time will still not be formatted correctly). The only way I get get just the time is to retype every cell. What am I doing wrong? I am using Excel 2002 SP3 TIA, Cindy When data is downloaded from the web, a lot of "other&quo...

organizing hotmail emails within outlook 2003
At home I have outlook 2003 on windows xp configured so that my hotmail email is picked up in outlook. I have outlook on exchange at work and have found that organizing my email by color is invaluable. I tried to organize my hotmail email by color and with rules, however outlook will not let me do so. Is there a way around this? If I could get the mail in my hotmail account into the main default "inbox" under personal folders in outlook then I can organize the email how I want to, however when I try to do this I have no success in doing so. colors are controlled by views, not r...

Copy/paste range of rows between 2 dates...
Hi! I have a sheet called data which act as a database. The column A has the dates. In order to create customized chart in a userform, for different range of data(i.e from column D, G and M...), I'd like to select a range of rows that are between 2 dates and create the charts accordingly. Or copy to range to another sheet and then create the charts. I am not so advanced in VBA and any help would be greatly appreciated. Thanks! Greg ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore! Free Excel Forum http://www.ozgrid.com/forum *** Hi ...

how to insert borders on flyer within Microsoft publisher?
Please help, I'm trying to insert a border around a flyer...I'm using Microsoft Publisher. Thanks, Harriet Is it a clipart border, Borderart or a simple rectangle? What problems are you having? What version Publisher? Any border you insert should be sent to the back so it does not interfere with your main design. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Harriet" <Harriet@discussions.microsoft.com> wrote in message news:7E2ED4D8-CC09-497F-A17C-44AE41F951C9@microsoft.com... > Please help, I'm ...

Calculating dates #3
Can anyone tell me what I should use (in the way of helper cells) to take any date (mm/dd/yyyy) and turn it into that same month and day for specific year? For instance, turn... 10/12/2009 into 10/12/2010 and 4/6/1998 into 4/6/2010 I'm trying to determine anniversary date based on start date and do it starting in 2010. TIA -- Jordon Try this: =3DDATE(2010,MONTH(A1),DAY(A1)) Assuming your date is in A1. Hope this helps. Pete On Jan 5, 5:35=A0pm, Jordon <jordon@REMOVE~THISmyrealbox.com> wrote: > Can anyone tell me what I should use (in the way of helper cells) > to tak...

how do I format cells to change date and time to just date
I want to format a column that contains date and time and I want it to show just the date and not the time. Going into format and clicking on the date and changing doesnt work. Probably the cells aren't really dates, but text. You can check with the ISTEXT() function. If they are text, formatting doesn't have any effect. -- Kind regards, Niek Otten Microsoft MVP - Excel "bondam" <bondam@discussions.microsoft.com> wrote in message news:F4D61EC1-30E2-4723-89E1-F47B545818EE@microsoft.com... >I want to format a column that contains date and time and I want it t...

User list
I'm wondering how to check the users on Exchange Server 2003, I tried it on E2k3 by [PS] W:\>Get-Mailbox -server Exchange2k3, and I got nothing in the list, but when I tried to remove Exchange Server 2003, I got an error, The component "Microsoft Exchange Messaging and Collaboration Services" cannot be assigned the action "Remove" because: - One or more users currently use a mailbox store on this server. These users must be moved to a mailbox store on a different server or be mail disabled before uninstalling this server. Any idea? Thanks. I followed the instruc...

programmatically generate xsl
I am trying to write an application that will generate XSL files (trying to automat some of my development), but am having a heck of a time. I just don't fully grasp the namespace issues I am having. This is what I have so far: ---------------------------------Code XmlDocument doc = new XmlDocument(); doc.AppendChild(doc.CreateXmlDeclaration ("1.0","UTF-8", string.Empty)); XmlNode root = doc.AppendChild(doc.CreateElement ("xsl:stylesheet","http://www.w3.org/1999/XSL/Transform")); XmlAttribute attr = doc.CreateAttribute("version"); attr.Inn...

How to remove locations from the drop-down list?
My drop down list for appointment locations contains a few duplicate entries, usually because one of the duplicates contains a spelling error. How can I get rid of these false entries so that I don't always have to be super-cautious to pick the correct one? M. "Michael Moser" <michael.nospam.moser@nospam.freesurf.ch> wrote in message news:DF17B5F2-DA57-48A7-B7DD-3B5A52F2A4E6@microsoft.com... > My drop down list for appointment locations contains a few duplicate > entries, usually because one of the duplicates contains a spelling error. > How ...

Posting a Journal Entry directly Into SQL?
Currently, we using Integration Assistant 8.0 for Excel to import about 6 Simple Journal Entries per day. This was fairly easy for the first few weeks, but now it's just getting boring. Our process goes something like this: Run a custom SQL Query (that operates on both Great Plains and other data sources) that produces a result set that is cut and pasted as a formatted Journal Entry into Excel. Then, from Excel, we just run our already-built integration package which imports the batch into Great Plains. We've already figured out how to get some basic transaction data out of Gr...

PAB DOMAIN CHANGE for hundreds of address entries
Anyone know how to change all email domains in the PAB file at once? My company name changed with our emil domain and I have over 1000 entries. I'm looking to make a universal change, but cannot figure out how to import changes to PAB file. I can import to CONTACTS but not PAB, which I keep separate. Thanks. .. >Anyone know how to change all email domains in the PAB >file at once? Outlook 2000 and up don't use the PAB. -- Brian Tillman Smiths Aerospace 3290 Patterson Ave. SE, MS 1B3 Grand Rapids, MI 49512-1991 Brian.Tillman is the name, smiths-aerospace.com is ...