Enter the Microsoft Project formula. Instead of changing flag fields by hand, you can have a formula do it for you based on the values in your project plan.
Just like formulas in Excel, MS Project formulas can look at the data in your project plan and dynamically adjust the value of any flag, text or number column. This gives you a lot of flexibility to set up import rules, apply them to a flag field, and then have OnePager Pro automatically import the tasks and milestones that meet your criteria.
To add a formula to a flag field, just follow these steps:
- Right-click on the flag field you want to customize and choose Custom Fields.
- Under the Custom Attributes section, click the Formula button.
- A formula editor will appear, allowing you to insert different fields and conditions. We’ll show you a few examples later in this article.
- After you’ve written your formula, you will have the option to apply it to summary tasks as well. This is highly recommended, especially if you are creating an executive summary report. Our recommended setting for summary tasks is the Rollup option, with the dropdown set to “OR”. This means that if any child tasks have their flag set to yes, the summary task will be brought in as well.
- Click OK again, and the formula will be applied to all of the tasks and summary tasks (if you chose to roll-up) in your project plan.
As the values in your project plan change, the formula will automatically update the flag column. So, for example, if you want to create a OnePager Pro chart for all tasks assigned to Randy, and you change a task’s assignment from Randy to Jack, the formula will de-flag the task, and the task will disappear from OnePager Pro when you do an update. Pretty neat, right?
Example Microsoft Project Formulas
So, what kind of formulas can you create in Microsoft Project? The sky is the limit, and you don’t have to be a developer or a propeller-head to write one. Here are some examples:
Formula: IIf([Text1]=”Converter”,”Yes”,”No”)
Meaning: Set the flag to yes for any tasks where the Text1 field is set to “Converter”. This is a great way to filter tasks based on a pre-determined category.
Formula: IIf([Summary]=True,IIf([Outline Level]=2,”Yes”,”No”),”No”)
Meaning: Set the flag to yes for any summary-level tasks at outline level #2. If you want to show an executive-level summary of your project without hand-selecting summary tasks, this is a good way to quickly grab a specific level of detail.
Formula: IIf([Start]>=CDate(“9/14/2013″) AND [Start]<=CDate(“9/28/2013″),”Yes”,”No”)
Meaning: Set the flag to yes for any tasks that start between 9/14/2013 and 9/28/2013. This formula is good for identifying tasks that are starting during a given period of time, and can help you focus your team on what to do next.
As you can see, there are limitless possibilities when it comes to writing these formulas. Need to identify tasks that are late, over-budget, or at risk? Let your PM tools do the heavy lifting for you. The formulas in MS Project will find the tasks for you, and OnePager Pro will present them beautifully.
15-Day
Free Trial