Custom Formula in a Summary Task

[Brian Lukanic]

I want to use a formula in my summary tasks to read from its summary tasks
and display data in a text field depending on the name of the subtask. Let me
explain this in pseudocode:

Let's use Text20 as the column that contains the formula. I will also make
sure to check the option that requires summary tasks to inherit the formula.

The formula in the subtask would do this:
1) look at all child tasks (subtasks.) I think that "IIf([Summary]=-1" gets
me there, correct?
2) Then, If a subtask [Name] = "Analysis", then display the Finish field of
that subtask's row onto Text 20 of the summary task. Else, continue until all
subtasks are checked.
3) Only one subtask will have this name, so that formula can either stop
once it finds a hit, or it can cycle through the rest of the subtasks and
ingore all that fail to meet the criteria.

In my application, I am using subtasks to show phases. I want my summary
task to ultimately have its own column for analysis completion date, QA
completion date, etc. Because I want this data on one row on the summary, I
need a formula tocompute those and populate the fields. I have experimented
with the "copy cell & paste link" option, which works just as well, but I
want this summary task to be a template for my team, and as soon as you
copy/paste the task you lose dynamic updating.

Any help in coming up with the syntax on this formula would be appreciated.

 

[Jan De Messemaeker]

Hi,

I don't think formaulas can solve this for you, I think you need VBA coding.

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf

 

[Brian Lukanic]

OK so assuming that I can't use a formula in the formatting window and
instead need to use VBA, I have a followup question: Is it still possible to
apply this VBA formula to an entire column so that the column constantly has
the formula applied? I don't want to have to manually run a macro to get this
to work, for instance.

I do have Rod Gill's book and can probably struggle through the coding on
this, but I might need some pointers too.

Hi,

I don't think formaulas can solve this for you, I think you need VBA coding.

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf

I want to use a formula in my summary tasks to read from its summary tasks
and display data in a text field depending on the name of the subtask. Let
me
explain this in pseudocode:

Let's use Text20 as the column that contains the formula. I will also make
sure to check the option that requires summary tasks to inherit the
formula.

The formula in the subtask would do this:
1) look at all child tasks (subtasks.) I think that "IIf([Summary]=-1"
gets
me there, correct?
2) Then, If a subtask [Name] = "Analysis", then display the Finish field
of
that subtask's row onto Text 20 of the summary task. Else, continue until
all
subtasks are checked.
3) Only one subtask will have this name, so that formula can either stop
once it finds a hit, or it can cycle through the rest of the subtasks and
ingore all that fail to meet the criteria.

In my application, I am using subtasks to show phases. I want my summary
task to ultimately have its own column for analysis completion date, QA
completion date, etc. Because I want this data on one row on the summary,
I
need a formula tocompute those and populate the fields. I have
experimented
with the "copy cell & paste link" option, which works just as well, but I
want this summary task to be a template for my team, and as soon as you
copy/paste the task you lose dynamic updating.

Any help in coming up with the syntax on this formula would be
appreciated.

 

[Jim Aksel]

Sorry, the formula function or the vba code will drive the field, not both.
You'll need a macro. You can set up the marcro to run every time something
is changed anwhere in the project... but that is somewhat overkill and will
slow you down.

Also, when coding, you need to consider the instances when the subtask
"Analysis" never appears, and if it appears more than once.

Something else too. You probably only want to address this issue at the
lowest level of indenture. For example, if you have multiple levels you will
need to address how to roll this all up multiple times.

--
If this post was helpful, please consider rating it.

Jim Aksel, MVP

Check out my blog for more information:
http://www.msprojectblog.com

OK so assuming that I can't use a formula in the formatting window and
instead need to use VBA, I have a followup question: Is it still possible to
apply this VBA formula to an entire column so that the column constantly has
the formula applied? I don't want to have to manually run a macro to get this
to work, for instance.

I do have Rod Gill's book and can probably struggle through the coding on
this, but I might need some pointers too.

Hi,

I don't think formaulas can solve this for you, I think you need VBA coding.

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf

I want to use a formula in my summary tasks to read from its summary tasks
and display data in a text field depending on the name of the subtask. Let
me
explain this in pseudocode:

Let's use Text20 as the column that contains the formula. I will also make
sure to check the option that requires summary tasks to inherit the
formula.

The formula in the subtask would do this:
1) look at all child tasks (subtasks.) I think that "IIf([Summary]=-1"
gets
me there, correct?
2) Then, If a subtask [Name] = "Analysis", then display the Finish field
of
that subtask's row onto Text 20 of the summary task. Else, continue until
all
subtasks are checked.
3) Only one subtask will have this name, so that formula can either stop
once it finds a hit, or it can cycle through the rest of the subtasks and
ingore all that fail to meet the criteria.

In my application, I am using subtasks to show phases. I want my summary
task to ultimately have its own column for analysis completion date, QA
completion date, etc. Because I want this data on one row on the summary,
I
need a formula tocompute those and populate the fields. I have
experimented
with the "copy cell & paste link" option, which works just as well, but I
want this summary task to be a template for my team, and as soon as you
copy/paste the task you lose dynamic updating.

Any help in coming up with the syntax on this formula would be
appreciated.

 

[Jack Dahlgren]

I think it is possible with a couple of custom fields and a formula
For each sub task we need a flag field (flag the analysis task)
For each sub task we need a calculated field which checks the flag field and
if the task is an analysis task it is set equal to that task finish and if
not is set to the project start date or some other low value.

iif([Flag1],[Finish],[Project Start])

The summary task is then set to roll-up the Max value which will be the
value set in the Analysis Task.

You would have to make a few of these if you want to handle different pieces
of information (QA etc.)

This can all be done with a formula and flags - and sometimes without the
flag fields, but I think you will need them.

-Jack Dahlgren
http://zo-d.com/blog

 

[Brian Lukanic]

Ok thanks very much. I think what I'll do is write a macro to run the VBA
script on close, for instance, or some other way once per open session.

That being said, I think I am going to need some assistance with the syntax
for the VBA script. If somebody would be able to point me in the right
direction I would appreciate it. Below is the pseudocode that I would want it
to do:

1) For every summary task, look at the four child tasks below it.
2) If the child tasks contains the word "Analysis," grab the Finish date
information and put it into Text20 of the Summary task. Else keep going.
3) If the child task contains the word "QA," grab the Finish date
information and put it into Text21 of the Summary task. Else keep going.
4) When the last child task is reviewed, end the loop.

 

[Jack Dahlgren]

Did you see my post?
You can do this without VBA.

-Jack Dahlgren

Ok thanks very much. I think what I'll do is write a macro to run the VBA
script on close, for instance, or some other way once per open session.

That being said, I think I am going to need some assistance with the syntax
for the VBA script. If somebody would be able to point me in the right
direction I would appreciate it. Below is the pseudocode that I would want it
to do:

1) For every summary task, look at the four child tasks below it.
2) If the child tasks contains the word "Analysis," grab the Finish date
information and put it into Text20 of the Summary task. Else keep going.
3) If the child task contains the word "QA," grab the Finish date
information and put it into Text21 of the Summary task. Else keep going.
4) When the last child task is reviewed, end the loop.

I want to use a formula in my summary tasks to read from its summary tasks
and display data in a text field depending on the name of the subtask. Let me
explain this in pseudocode:

Let's use Text20 as the column that contains the formula. I will also make
sure to check the option that requires summary tasks to inherit the formula.

The formula in the subtask would do this:
1) look at all child tasks (subtasks.) I think that "IIf([Summary]=-1" gets
me there, correct?
2) Then, If a subtask [Name] = "Analysis", then display the Finish field of
that subtask's row onto Text 20 of the summary task. Else, continue until all
subtasks are checked.
3) Only one subtask will have this name, so that formula can either stop
once it finds a hit, or it can cycle through the rest of the subtasks and
ingore all that fail to meet the criteria.

In my application, I am using subtasks to show phases. I want my summary
task to ultimately have its own column for analysis completion date, QA
completion date, etc. Because I want this data on one row on the summary, I
need a formula tocompute those and populate the fields. I have experimented
with the "copy cell & paste link" option, which works just as well, but I
want this summary task to be a template for my team, and as soon as you
copy/paste the task you lose dynamic updating.

Any help in coming up with the syntax on this formula would be appreciated.

 

[Brian Lukanic]

That worked! I was apparently very close on my own but was using a text
field, where I should have been using a date field so that the date would
roll up to the summary. This works elegantly and does exactly what I need.

thanks very much!

 

[Jack Dahlgren MVP]

Glad I could help.

-Jack Dahlgren

That worked! I was apparently very close on my own but was using a text
field, where I should have been using a date field so that the date would
roll up to the summary. This works elegantly and does exactly what I need.

thanks very much!

I want to use a formula in my summary tasks to read from its summary
tasks
and display data in a text field depending on the name of the subtask.
Let me
explain this in pseudocode:

Let's use Text20 as the column that contains the formula. I will also
make
sure to check the option that requires summary tasks to inherit the
formula.

The formula in the subtask would do this:
1) look at all child tasks (subtasks.) I think that "IIf([Summary]=-1"
gets
me there, correct?
2) Then, If a subtask [Name] = "Analysis", then display the Finish field
of
that subtask's row onto Text 20 of the summary task. Else, continue until
all
subtasks are checked.
3) Only one subtask will have this name, so that formula can either stop
once it finds a hit, or it can cycle through the rest of the subtasks and
ingore all that fail to meet the criteria.

In my application, I am using subtasks to show phases. I want my summary
task to ultimately have its own column for analysis completion date, QA
completion date, etc. Because I want this data on one row on the summary,
I
need a formula tocompute those and populate the fields. I have
experimented
with the "copy cell & paste link" option, which works just as well, but I
want this summary task to be a template for my team, and as soon as you
copy/paste the task you lose dynamic updating.

Any help in coming up with the syntax on this formula would be
appreciated.