Summary Row Delta Against Its Sub-Tasks

[Twenny02]

Hi,

I have a MSP programme plan that imports data from a large Excel spreadsheet and updates the task/milestone lines using an import map. These lines are grouped in the Excel doc by the project they relate to, with approx 200 projects.

The Excel doc is basically a flat file, and does not contain any project name header rows.

The MS Project programme plan I import into has the structure set up with manually inserted summary rows to represent the project names (headers). Beneath these summary rows are the tasks / milestones that are updated by the import from the Excel sheet.

The issue I have is that the summary rows can become mis-aligned with the sub-tasks from the Excel data, e.g. if the project status changes in the Excel sheet, it will update for each sub-task in the MSP programme plan (the project status is repeated against each related task/milestone line in the Excel doc), but it will not update for the summary task as the summary task does not exist in the Excel doc. This has to be changed manually.

Is there a way to automatically flag that data in a specific column for a Summary row is different to the data in the same column for its sub-tasks?

Any guidance much appreciated!

Thanks

 

[JulieInMaine]

Is there a reason why you are using manually scheduled summary tasks? If you switch the summary tasks from manual to autoscheduled, the start, finish, duration is automatically calculated for the summary tasks based upon subtask data.

 

[Twenny02]

Thanks,

I am in 2007 though so not using manual scheduling. There are no issues with the summary row duration or dates, but other customised columns on the same row as the summary task.

For example there is a column called 'Project Status'. This shows the basic status of the project such as In-Flight or On-Hold. The sub tasks can get this from the Excel datasheet import as the project status is repeated against each item in the Excel sheet. However, as the summary row does not exist in the Excel data sheet it does not update as part of the import, so consequently may become out of sync with the actual project status. I just need to see if I can automate a flag to highlight if the value in customised columns such as Project Status is different for the summary row compared with all the sub-task rows beneath.

For example the summary row may have 'On Hold' in the project status column as it was manually entered a few weeks ago, but the sub-tasks are taking the latest project status from the Excel sheet which shows 'Initiated'.

It is important that the summary row has this customised data aligned, as I use the summary row to display key data such as project status in various gantt views that are rolled up to show the summary bar.

 

[JulieInMaine]

Sorry I mis-read the "manually inserted summary tasks" as manually scheduled summary tasks.

Is the status of the tasks user defined or derived from some math? If it is user defined, in the master, set a roll up for the summary fields. So for example - if the "on-hold" status at subtask level equals zero you can sum those values at summary level and display "On hold" in another if the sum equals zero.

If the status is user defined you'll need some VBA code to check the status of each subtask and then make decisions about what the status should be. Unfortunately, formulas in fields (customized columns) cannot reference data in other fields. But if you can work out the math (zero for all subtasks equals "On Hold", greater than 1 but less than X equals "Initiated", etc) you won't need the VBA.

 

[Twenny02]

Thanks Julie,

See what you mean re the Summary Roll Up - that may be an option for me.

cheers for your help.

J

 

[JulieInMaine]

You're welcome. Post back if you have further questions.