Copy and use this spreadsheet
Create a copy of this spreadsheet for your Google Apps account
File structure
My Google Spreadsheets file has two woorksheet WBS (work breakdown structure of the project) and User View (a list with resources to assign to each task.)
Work breakdown structure
In WBS sheet you have the following columns: WBS (task ID), Task (task description), Pred (predecessor), % (percentage of completion), Owner (resource assiged to the task), Role (owner role);
Owner role is calculated looking for the name insert into the column Owner into the sheet User View using VLOOKUP function (vertical lookup), for example:
=vlookup(E5,'user view'!A:B,2,)
...it search the value contained into the cell E5 on the sheet user view in a colum's interval from A to B, and return - if the value in E5 exist in the column A - the result from the column with index 2 (column B).
Sheet WBS has also other columns like star date, finish date, re-planned star date, re-planned finish date, project delay, and a section to assign a variable cost + fixed cost + a manual adjust to each task.
Take a look at the spreadsheet for more info about all columns.
Resources
All resources (task's owner) are located into a second worksheet User view. I have added some basic columns and the hour cost for the resource.
Total Task is calculated automatically from the sheet. I have simply added a function, COUNTIF, that count how time an user si repeted into the sheet WBS
=countif(wbs!E:E,A3)
... it updates the counter if finds in the column E of the sheet WBS a value equal to the value contained into the cell A3.
Excel or Google Spreadsheet?
Sincerly, I found Google Spreedsheets a good application to create spreadsheets with a medium level of complexity, but the "response", for an Excel-addicted, is not the same. In any case it's a useful service and, above all, it's free!
No comments:
Post a Comment