Our company has a progress tracking excel for team members on site to tabulate when the scheduled tests are completed. The spreadsheet is broken down to the tests required of every equipment (i.e. chiller, cooling tower, chilled water pipes etc.), tracking the progress and the paperwork of all the tests. On the other hand, team leaders will be given licenses to MS Project for client update meeting to show the overall progress of every equipment (=tasks).
In this setting, MS Project is not interested in the micro details as they are the interest of the team members. So the equipment (tasks) overall progress (% completed) will be averaged.
Is there any way that the said overall progress (% completed) can be linked to the MS Project from MS Excel so that the team leader will not have to key the % completed manually every time prior to the client meeting? When double-click a task, the input box of % completed can only be a number between 1 to 100 (%) while a hyperlink from MS Excel is desired.
This enables team leader (who holds more than one project most of the time):
– to better sync with the team members of respective sites
– to know ALL the site progress when using MS Project
Thank you in advance!!
Linking % complete field to a spreadsheet is not possible in Project. Alternatively, you need to use custom fields and macro to solve this requirement. First, in the custom fields (text1, text2, ..) have the file location of the spreadsheet and the cell range or address that has test results of each equipment. Second, write a macro to calculate the overall progress for each equipment by using the details of the custom fields and updates the % complete field.