Linking Tasks to aggregate % complete

%3CLINGO-SUB%20id%3D%22lingo-sub-3298831%22%20slang%3D%22en-US%22%3ELinking%20Tasks%20to%20aggregate%20%25%20complete%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3298831%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20group%20of%2010%20tasks%20%2C%20say%20group%201%20tasks.%20These%2010%20tasks%20have%20a%20hierarchy%20among%20themselves.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20another%20group%20of%20100%20tasks%2C%20say%20group%202%20tasks.%20These%20100%20tasks%20have%20a%20hierarchy%20among%20themselves%20too.%26nbsp%3B%3C%2FP%3E%3CP%3EGroup%201%20tasks%20are%20co-located.%20Group%202%20tasks%20are%20co-located%20and%20they%20do%20not%20overlap.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20%25%20Complete%20of%20each%20of%20the%20group%201%20tasks%2C%20should%20be%20a%20roll%20up%20of%20a%20few%20of%20group%202%20tasks.%26nbsp%3B%20These%20group%202%20tasks%2C%20can%20be%20located%20anywhere%20within%20the%20group%202%20bundle%2C%20i.e.%20they%20won't%20be%20located%20sequentially.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20link%20a%20particular%20group%201%20task%20with%20a%20set%20of%20group%202%20tasks%2C%20to%20get%20an%20aggregated%20%25%20complete%20value%20%3F%26nbsp%3B%20I%20can't%20put%20group%201%20task%20as%20a%20summary%20task%20within%20group%202%20block%20because%20that%20would%20make%20group%201%20%26amp%3B%20group%202%20overlap.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20just%20link%20group%201%20%26amp%3B%20group%202%20tasks%2C%20it%20impacts%20the%20dates%20and%20not%20the%20%25%20complete%20aggregation.%20So%20how%20do%20I%20link%20two%20tasks%2C%20without%20a%20Summary%20-%20Sub%20task%20relationship%20to%20roll%20up%20the%20%25%20complete%20%3F%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20thoughts%20%3F%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20hack%2C%20which%20I%20can%20think%20of%20is%2C%20if%20I%20can%20create%20a%20dummy%20group%202%20task%2C%20which%20mirrors%20the%20actual%20task%20attributes%20of%20real%20group%202%20task%20and%20place%20it%20within%20group%201%20block%2C%20Group%201's%20task's%20%25%20complete%20can%20be%20aggregated.%20But%20is%20there%20a%20way%20to%20%22link%22%20two%20tasks%2C%20so%20that%20one%20task%20mirrors%20other%20tasks%20attribute%20-%20like%2C%20if%20I%20change%20the%20task%20name%20%2F%20duration%20%2F%20%25%20complete%20etc.%20of%20one%20task%2C%20it%20gets%20changed%20in%20other%20task%20too%20%3F%20something%20like%20in%20excel%20linking%20of%20two%20cells.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20thoughts%20on%20this%20too%20%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3299460%22%20slang%3D%22en-US%22%3ERe%3A%20Linking%20Tasks%20to%20aggregate%20%25%20complete%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3299460%22%20slang%3D%22en-US%22%3ENkumar2010%2C%3CBR%20%2F%3EYou%20mention%20two%20files%2C%20one%20called%20Master.mpp%20and%20one%20called%20Vendor.mpp.%20Are%20you%20working%20with%20a%20linked%20structure%20(e.g.%20Vendor.mpp%20inserted%20as%20a%20subproject%20into%20the%20Master.mpp)%3F%20If%20so%2C%20that's%20a%20different%20ballgame%2C%20still%20workable%20but%20more%20complicated.%20The%20sample%20file%20you%20provided%20shows%20what%20I%20was%20expecting%20to%20see%2C%20a%20single%20file%20with%20a%20summary%20group%20of%20%22group%201%20tasks%22%20and%20a%20summary%20group%20of%20%22group%202%20tasks%22.%3CBR%20%2F%3E%3CBR%20%2F%3EAre%20you%20overcomplicating%20a%20solution%3F%20Perhaps.%20As%20I%20though%20about%20it%20overnight%2C%20the%20obvious%20solution%20is%20to%20restructure%20your%20file%2C%20if%20it%20is%20in%20fact%20a%20single%20file%2C%20such%20that%20the%20associated%20groups%20of%20group%202%20tasks%20are%20sub-tasks%20of%20appropriate%20group%201%20tasks.%20Then%20the%20whole%20percent%20complete%20idea%20is%20automatic.%20However%2C%20I%20get%20the%20feeling%20that%20for%20some%20reason%20that%20approach%20just%20doesn't%20work%20for%20you.%3CBR%20%2F%3E%3CBR%20%2F%3EOkay%2C%20since%20your%20question%2Fissue%20is%20rather%20unique%20I%20think%20it%20would%20be%20better%20to%20work%20with%20you%20one-on-one.%20You%20can%20contact%20me%20at%20the%20address%20below.%20I%20will%20ask%20some%20questions.%3CBR%20%2F%3EJohn%3CBR%20%2F%3Ejmacprojataticlouddotdotcom%3CBR%20%2F%3E(remove%20obvious%20redundancies)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3299450%22%20slang%3D%22en-US%22%3ERe%3A%20Linking%20Tasks%20to%20aggregate%20%25%20complete%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3299450%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F917748%22%20target%3D%22_blank%22%3E%40John-project%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAllow%20me%20to%20explain%20the%20complete%20scenario%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EScenario%20%3A%3CBR%20%2F%3EThere%20is%20a%20Master.mpp%20and%20Vendor.mpp%3CBR%20%2F%3EMaster.mpp%20contains%20high%20level%20task%2C%20which%20roll%20up%20as%20summary%20tasks.%20Vendor.mpp%20contains%20low%20level%20tasks%2C%20which%20roll%20up%20as%20summary%20tasks.%3CBR%20%2F%3EUnfortunately%2C%20the%20summary%20tasks%20in%20Vendor.mpp%20does%20not%20neatly%20align%20to%20tasks%20in%20Master.mpp.%20Tasks%20in%20vendor.mpp%20only%20align%20logically%20to%20tasks%20in%20Master.mpp.%3C%2FP%3E%3CP%3EHence%20the%20challenge%20is%20how%20to%20get%20%25%20complete%20for%20tasks%20in%20Master.mpp.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDuration%20of%20tasks%20in%20Master.mpp%20is%20bulk%20duration%2C%20which%20vendor.mpp%20tasks%20%26amp%3B%20summary%20task%20must%20adhere%20to.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESolution%20I%20thought%20%3A%3CBR%20%2F%3EIf%20I%20take%20the%20tasks%20from%20Master.mpp%20and%20place%20it%20in%20Vendor.mpp%20say%2C%20as%20a%20Group%20Task%201)%20and%20link%20the%20summary%20tasks%20%26amp%3B%20subtask%20which%20are%20native%20to%20Vendor.mpp%20to%20the%20Group%20Task%201%20to%20which%20they%20logically%20correlate%2C%20the%20%25%20complete%20can%20flow%20up.%20But%20then%2C%20linking%20tasks%20just%20moves%20the%20dates%2C%20not%20%25%20complete.%20Moreover%2C%20tasks%20from%20Master.mpp%20has%20larger%20duration%2C%20say%204%20months%2C%20but%20the%20tasks%20in%20Vendor.mpp%20has%20duraiton%20in%20days%20%26amp%3B%20weeks.%20If%20we%20link%20them%20in%20any%20ways%2C%20what's%20the%20impact%20on%20duraiton%20of%20Group%201%20tasks%20(i.e.%20Master.mpp%20tasks)%20%3F%3C%2FP%3E%3CP%3EIs%20there%20a%20simple%20solution%20to%20the%20scenario%20%3F%20Am%20i%20overcomplicating%20a%20solution%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20is%20the%20sample%20file.%20Since%20.mpp%20attachment%20is%20not%20supported%2C%20please%20rename%20the%20attached%20file%20from%20Sample.xls%20to%20Sample.mpp%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3299251%22%20slang%3D%22en-US%22%3ERe%3A%20Linking%20Tasks%20to%20aggregate%20%25%20complete%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3299251%22%20slang%3D%22en-US%22%3ENKumar2010%2C%3CBR%20%2F%3EOkay%2C%20thanks%20for%20the%20full%20explanation%2C%20it's%20what%20I%20assumed%20but%20it's%20always%20good%20to%20check.%20And%20yes%2C%20I%20can%20develop%20a%20way%20to%20do%20it%20but%20I%20need%20more%20details.%20Would%20you%20be%20able%20to%20send%20me%20your%20file%3F%3CBR%20%2F%3E%3CBR%20%2F%3EJohn%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3299240%22%20slang%3D%22en-US%22%3ERe%3A%20Linking%20Tasks%20to%20aggregate%20%25%20complete%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3299240%22%20slang%3D%22en-US%22%3ELet%20me%20give%20it%20a%20shot.%20Please%20see%20my%20reply%20above%20to%20John's%20post%20to%20get%20some%20clarity.%20How%20do%20I%20do%20step%202%20i.e.%20add%20a%20look%20up%20table%20to%20a%20custom%20field%20%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3299237%22%20slang%3D%22en-US%22%3ERe%3A%20Linking%20Tasks%20to%20aggregate%20%25%20complete%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3299237%22%20slang%3D%22en-US%22%3EBy%20%25%20aggregated%20complete%2C%20I%20mean%20the%20rollup%20%25%20complete.%20The%20way%20a%20summary%20task's%20%25%20complete%20is%20a%20roll%20up%20value%20of%20sub%20tasks%20%25%20Complete.%20Just%20that%20in%20my%20situation%2C%20the%20tasks%20which%20should%20contribute%20to%20the%20aggregated%20%25%20complete%20of%20the%20higher%20task%2C%20are%20not%20the%20sub-tasks%20of%20this%20higher%20task.%20They%20are%20physically%20distributed%20within%20the%20file.%3CBR%20%2F%3EIn%20a%20normal%20world%2C%20Summary%20Task%20A%2C%20will%20have%20rolled%20up%20%25%20Complete%20values%20of%20it's%20child%20tasks.%3CBR%20%2F%3EIn%20my%20case%2C%20Summary%20Task%20A%2C%20wants%20to%20have%20it's%20%25%20complete%20values%20from%20tasks%20which%20are%20not%20it's%20own%20child%20task%2C%20but%20are%20located%20elsewhere%20in%20the%20file%2C%20and%20are%20other%20summary%20tasks%20child.%3CBR%20%2F%3E%3CBR%20%2F%3EIs%20there%20a%20way%20to%20do%20it%20%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3298890%22%20slang%3D%22en-US%22%3ERe%3A%20Linking%20Tasks%20to%20aggregate%20%25%20complete%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3298890%22%20slang%3D%22en-US%22%3ENKumar2010%20--%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20I%20understand%20your%20question%20correctly%2C%20I%20believe%20you%20are%20trying%20to%20calculate%20the%20%25%20Complete%20values%20for%20selected%20groups%20of%20tasks.%20Here%20is%20something%20you%20might%20try%3A%3CBR%20%2F%3E%3CBR%20%2F%3E1.%20Create%20a%20custom%20Text%20field%20named%20Task%20Group.%3CBR%20%2F%3E2.%20Add%20a%20lookup%20table%20to%20this%20custom%20field%20and%20include%20values%20like%20Group%201%2C%20Group%202%2C%20etc.%3CBR%20%2F%3E3.%20Insert%20this%20custom%20field%20in%20any%20Task%20view%2C%20such%20as%20the%20Gantt%20Chart%20view.%3CBR%20%2F%3E4.%20In%20the%20custom%20column%2C%20specify%20a%20value%20for%20each%20task%2C%20like%20Group%201%2C%20Group%202%2C%20etc.%3CBR%20%2F%3E5.%20To%20the%20right%20of%20the%20custom%20column%2C%20insert%20the%20%25%20Complete%20column%20as%20well.%3CBR%20%2F%3E5.%20Click%20the%20AutoFilter%20pick%20list%20button%20in%20the%20column%20header%20of%20the%20custom%20column%20and%20then%20select%20the%20Group%20on%20this%20Field%20item%20on%20the%20pick%20list%20menu.%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20you%20look%20at%20the%20%25%20Complete%20column%2C%20you%20should%20see%20the%20aggregated%20%25%20Complete%20value%20for%20each%20group%20of%20tasks.%20Just%20a%20thought.%20Hope%20this%20helps.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3298876%22%20slang%3D%22en-US%22%3ERe%3A%20Linking%20Tasks%20to%20aggregate%20%25%20complete%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3298876%22%20slang%3D%22en-US%22%3ENKumar2010%2C%3CBR%20%2F%3ECan%20something%20like%20this%20be%20done%3F%20Probably%2C%20but%20without%20some%20VBA%20you%20won't%20be%20able%20to%20use%20the%20built-in%20%25%20Complete%20field%20for%20the%20aggregated%20value%2C%20it%20will%20need%20be%20be%20shown%20in%20a%20custom%20field.%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20first%20question%20is%2C%20what%20exactly%20is%20your%20definition%20of%20%22aggregated%20%25%20complete%22%20(i.e.%20what%20is%20the%20formula)%3F%3CBR%20%2F%3E%3CBR%20%2F%3EExcel%20has%20a%20much%20more%20open%20format%20with%20respect%20to%20linking%20cells.%20Since%20Project%20has%20a%20very%20specific%20data%20structure%2C%20linking%20information%20between%20tasks%20is%20very%20limited.%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20you%20can%20provide%20a%20sample%20of%20your%20file%20structure%2C%20I%20can%20help%20you%20get%20what%20you%20want.%3CBR%20%2F%3EJohn%3C%2FLINGO-BODY%3E
Occasional Contributor

I have a group of 10 tasks , say group 1 tasks. These 10 tasks have a hierarchy among themselves. 

I have another group of 100 tasks, say group 2 tasks. These 100 tasks have a hierarchy among themselves too. 

Group 1 tasks are co-located. Group 2 tasks are co-located and they do not overlap.  

 

The % Complete of each of the group 1 tasks, should be a roll up of a few of group 2 tasks.  These group 2 tasks, can be located anywhere within the group 2 bundle, i.e. they won't be located sequentially. 

 

How do I link a particular group 1 task with a set of group 2 tasks, to get an aggregated % complete value ?  I can't put group 1 task as a summary task within group 2 block because that would make group 1 & group 2 overlap.

 

If I just link group 1 & group 2 tasks, it impacts the dates and not the % complete aggregation. So how do I link two tasks, without a Summary - Sub task relationship to roll up the % complete ? 

Any thoughts ?? 

 

A hack, which I can think of is, if I can create a dummy group 2 task, which mirrors the actual task attributes of real group 2 task and place it within group 1 block, Group 1's task's % complete can be aggregated. But is there a way to "link" two tasks, so that one task mirrors other tasks attribute - like, if I change the task name / duration / % complete etc. of one task, it gets changed in other task too ? something like in excel linking of two cells.  

 

Any thoughts on this too ? 

 

 

7 Replies
NKumar2010,
Can something like this be done? Probably, but without some VBA you won't be able to use the built-in % Complete field for the aggregated value, it will need be be shown in a custom field.

The first question is, what exactly is your definition of "aggregated % complete" (i.e. what is the formula)?

Excel has a much more open format with respect to linking cells. Since Project has a very specific data structure, linking information between tasks is very limited.

If you can provide a sample of your file structure, I can help you get what you want.
John
NKumar2010 --

If I understand your question correctly, I believe you are trying to calculate the % Complete values for selected groups of tasks. Here is something you might try:

1. Create a custom Text field named Task Group.
2. Add a lookup table to this custom field and include values like Group 1, Group 2, etc.
3. Insert this custom field in any Task view, such as the Gantt Chart view.
4. In the custom column, specify a value for each task, like Group 1, Group 2, etc.
5. To the right of the custom column, insert the % Complete column as well.
5. Click the AutoFilter pick list button in the column header of the custom column and then select the Group on this Field item on the pick list menu.

If you look at the % Complete column, you should see the aggregated % Complete value for each group of tasks. Just a thought. Hope this helps.
By % aggregated complete, I mean the rollup % complete. The way a summary task's % complete is a roll up value of sub tasks % Complete. Just that in my situation, the tasks which should contribute to the aggregated % complete of the higher task, are not the sub-tasks of this higher task. They are physically distributed within the file.
In a normal world, Summary Task A, will have rolled up % Complete values of it's child tasks.
In my case, Summary Task A, wants to have it's % complete values from tasks which are not it's own child task, but are located elsewhere in the file, and are other summary tasks child.

Is there a way to do it ?
Let me give it a shot. Please see my reply above to John's post to get some clarity. How do I do step 2 i.e. add a look up table to a custom field ?
NKumar2010,
Okay, thanks for the full explanation, it's what I assumed but it's always good to check. And yes, I can develop a way to do it but I need more details. Would you be able to send me your file?

John

@John-project 

 

Allow me to explain the complete scenario 

 

Scenario :
There is a Master.mpp and Vendor.mpp
Master.mpp contains high level task, which roll up as summary tasks. Vendor.mpp contains low level tasks, which roll up as summary tasks.
Unfortunately, the summary tasks in Vendor.mpp does not neatly align to tasks in Master.mpp. Tasks in vendor.mpp only align logically to tasks in Master.mpp.

Hence the challenge is how to get % complete for tasks in Master.mpp.

 

Duration of tasks in Master.mpp is bulk duration, which vendor.mpp tasks & summary task must adhere to.

 

Solution I thought :
If I take the tasks from Master.mpp and place it in Vendor.mpp say, as a Group Task 1) and link the summary tasks & subtask which are native to Vendor.mpp to the Group Task 1 to which they logically correlate, the % complete can flow up. But then, linking tasks just moves the dates, not % complete. Moreover, tasks from Master.mpp has larger duration, say 4 months, but the tasks in Vendor.mpp has duraiton in days & weeks. If we link them in any ways, what's the impact on duraiton of Group 1 tasks (i.e. Master.mpp tasks) ?

Is there a simple solution to the scenario ? Am i overcomplicating a solution ?

 

Attached is the sample file. Since .mpp attachment is not supported, please rename the attached file from Sample.xls to Sample.mpp

Nkumar2010,
You mention two files, one called Master.mpp and one called Vendor.mpp. Are you working with a linked structure (e.g. Vendor.mpp inserted as a subproject into the Master.mpp)? If so, that's a different ballgame, still workable but more complicated. The sample file you provided shows what I was expecting to see, a single file with a summary group of "group 1 tasks" and a summary group of "group 2 tasks".

Are you overcomplicating a solution? Perhaps. As I though about it overnight, the obvious solution is to restructure your file, if it is in fact a single file, such that the associated groups of group 2 tasks are sub-tasks of appropriate group 1 tasks. Then the whole percent complete idea is automatic. However, I get the feeling that for some reason that approach just doesn't work for you.

Okay, since your question/issue is rather unique I think it would be better to work with you one-on-one. You can contact me at the address below. I will ask some questions.
John
jmacprojataticlouddotdotcom
(remove obvious redundancies)