Recently we had a user ask us how they might automatically populate their flag columns to allow a configurable start and end date constraint which would capture any task that starts, finishes, or is in the middle of execution. Essentially, the business requirements for the report looked like this:
The problem for us was the formula, as written, would not work due to the fact that we were working within a master project file with many subprojects. Here was our simple formula, which worked in the individual subprojects, but not if you looked at them all within the master file:
IIf([Finish]<CDate(“1/1/2014”) Or [Start]>CDate(“2/28/2014″),”No”,”Yes”)
RIDDLE SOLVED!
While we like to pride ourselves on our knowledge of Microsoft Project, we don’t always have the answers. In this case, some helpful folks in a couple of user groups on LinkedIn (links at the bottom of this post) politely pointed out that if you want a formula to work in a Master you must copy that formula to each subproject and the Master, which can most efficiently be accomplished using the Organizer. To reach the Organizer, go to File -> Info and click the Organizer button.
Once inside, simply ensure you’re working with the right source file where you formula exists, find the custom flag column that you’re trying to copy, and then copy it to each subproject and the master (or from the master to the subprojects if you build your formula there) using the dropdowns and you’re done.
At this time, your flag values will be set correctly, and you will be able to grab the correct tasks for your OnePager Pro report. Whenever you need to modify the formula, simply change the formula in your master, and all subprojects should pick up the change.
You can then further constrain the start and end boundaries of your visual within OnePager, as needed, within the Main tab of the Project-View Properties settings prior to presentation.
