One of the most common activities with large schedule files is filtering them down to show the most important tasks. In most cases, important means immediate and many industries (the construction industry for example) a 3-week look ahead schedule showing current and upcoming activities is very common.
Project's built-in filters allow you to filter based on a date range, but the date range filter asks for user input and you need to select the time periods. There is no way to easily automate this in VBA short of recreating and applying the filter, but in the more recent versions of project there is a very simple way to do this by using a custom field formula.
I've described how to set up custom field formulas before. (Please click the link and read about them if you are new to the topic). For filtering purposes, the simplest way is to use a Flag field. Flag fields have a value of either "Yes" or "No". You can set them manually, but here we are going to use a formula. The formula I am showing performs a few simple tests. First it checks for all tasks that begin before a date three weeks in the future. Then it removes any which are already complete. Of these tasks it selects all of those which are not finished yet. If the task meets those criteria, the field is marked "Yes". If it doesn't then the field remains as "No". Here is the formula:
IIf(([Start]<(Now()+21) And ([% Complete]<100)) And ([Finish]>Now()),"Yes","No")
If you paste this formula into a flag field (for example Flag1) then all you need to do is create a filter which shows any tasks for which Flag1 is "Yes". Note that the formula as written doesn't show any incomplete tasks which are supposed to be completed in the past. Theoretically if you have an updated schedule you won't have any tasks like that, but you can modify the logic to add that condition. Using this technique you should be able to get around almost any interactive filtering challenge.
Comments (1)
Very nice. I am new to VB and this is a great tool. I don't like the built-in date range filter because it does not do a nice clean job. Thanks,
Rob Dudley
P.S. do you have one that will look for items with-in a date range?
Rob, I guess it depends what you mean by "clean job". You can add additional criteria to exclude tasks which start before or end after the date range or practically anything you want. The first step is defining what you want. Write it down. Then derive the filter criteria which you need to achieve it. Some times it takes combining a few criteria to do it. Just enter each on a different line and set the "and" and "or" correctly. I can't be more specific unless you tell me exactly what you want -Jack
Posted by Robert A. Dudley | April 16, 2007 11:25 AM
Posted on April 16, 2007 11:25