Using logic to drive task column values

[Gordon_Smith]

Good afternoon,

I have a column which is set to a binary state, Active/Inactive as Text 2 (named Status, which is different than the Project Status field).  I also have Tasks with two indented subtasks (Project -> Subproject  -> Task).  Rather than me manually setting everything to Active/Inactive, can I use some logic to make updates?  i.e., If a Task is set to Active, the SubProject and Project should also be active.  Alternately, if all the tasks are inactive, the SubProject should also go inactive.

Can I also "lock" a task by Indention level (i.e. not directly set the Project Active/Inactive level, but rather only through its subtasks Active/Inactive settings)

I see there is a VBA component, which means my (rusty) Excel VBA should apply.  I suspect I could use some guidance on the syntax, however...

[#2]

Gordon_Smith,
First off, what version of Project are you using?

You show your hierarchy as, Project > Subproject > Task but I suspect it's not quite as you describe it. In Project, a subproject is a separate file inserted into a master. It can either be via dynamic linking or static (i.e. copied). A screen shot of your Gantt Chart view would help to clarify exactly what you have.

You are also using the terms "active" and "inactive". In Project those task attributes have specific meaning and probably not what you are doing. Again, the screen shot would help.

On the surface, yes it should be possible to do what you want. It may or may not require VBA. Let's see what you've got and then go from there.
John

[#3]

@John-project, Thanks for the kind reminder that I need to think in terms of Microsoft Project, and less the way we approach projects within Microsoft Project, particularly when asking for aid.  Allow me to try again 🙂

I am using Project Professional, via Microsoft Project Online Desktop Client, running on a local computer.

I have a variety of Tasks within a single MPP file, with Indented (sub)Tasks:

Indent 1: the overall contract (861 shown below)

Indent 2: a subaspect of the contract that is largely independent of other Indent 1 subaspects

Indent 3: A particular request of the client

Indent 4: tasks that support the request

I have created a column (Text 2) as a pulldown having the options of Active/Inactive so that I can quickly see if this is something we need to work on, or is paused/delivered.  If I change the value of Text 2 for a particulate Task to "Active" I would like to ensure that Text 2 for all the preceding Indentions follow suit.   This would give me an easy way to create a filtered view of projects that require our attention this week.

So, if I update Task (Indent 4) to Active (in Text 2)...

• Indent 3/Text 2 should also be updated to Active (but not other Indent 3 tasks) because if the Indent 4 task is active, so should the parent Indent 3 Request
• Indent 2/Text 2 should also be updated to Active (but not other Indent 2 tasks) because if the Indent 3 Request is active, so should the Indent 2 SubAspect "DKGP"
• Indent 1/Text 2 should also be updated to Active (but not other Indent 1 tasks) because if the Indent 2 SubAspect "DKGP" is active, so should the Indent 1 Contract 861.

The intent of this would be ensuring that we are paying attention to the Task, (while also ensuring we are providing the context of the Request, the SubAspect, and the Contract)

If, however, I update Task (Indent 4) to Inactive in Text 2...

• Any other Indent 4/Text 2 Tasks under the same parent Indent3 should not update
• If and only if all Indent 4/Text 2 values are Inactive for a particular parent Indent 3, then the parent Indent 3/Text 2 should update to Inactive. (if all the child indents are inactive, it makes sense for the parent to be inactive)
• If and only if all Indent 3/Text 2 values are Inactive for a particular parent Indent 2, then the parent Indent 2/Text 2 should update to Inactive. (if all the child indents are inactive, it makes sense for the parent to be inactive)
• If and only if all Indent 2/Text 2 values are Inactive for a particular parent Indent 1, then the parent Indent 1/Text 2 should update to Inactive. (if all the child indents are inactive, it makes sense for the parent to be inactive)

I thought about using % complete on the tasks, but felt there would be administrative confusions when reporting since a lot of times things change around on the fly, and I didn't want to deal with the relative percentages of the overall projects and explaining up above why/how those numbers kept changing.  Really was just interested in a binary statement

I have tried SubProject/Master MPPs before, and they quickly become difficult for my team to utilize properly, so I am trying a single MPP file approach this time.

Hopefully this didn't make it more confusing.  Thanks!

[#4]

Gordon,
No, you didn't make it more confusing, you made it much clearer. Good for you!

This could possibly be done with custom field formulas but it would likely take me longer to figure out the complex set of fields and formulas than to simply write a VBA procedure to do it. It's lunch time here, and hey, it's Sunday, so you'll have to wait till later today.

John

[#5 · Accepted solution]

@Gordon_Smith 

How about a simpler approach. You mentioned you want a quick-look filtered view. How about a simple custom filter. Here's a sample plan with [performance]task status (Text2) selected via the dropdown.

Now here's a filter for active tasks with the option to show related summary levels

Applying the filter gives this

Does that do what you need?

Buuuuuut, if that doesn't quite do everything you need, here is the macro code to implement all your criteria, at least if I understand it correctly.

John

[#6]

I think the filter will work great, and I appreciate the VBA Code in case I find other issues. Thanks!

[#7]

Gordon_Smith,
You're welcome and thanks for the feedback. Sometimes the simpler approach is best but I did have fun creating the VBA code 🙂
John