How to create MS Project task that auto extend if project is delayed
Someone asked me how I used to set up a task that is supposed to last the entire project (like Project Management task, or weekly meeting) and auto-update its end date if the project is delayed. Here is how to create such task:
I will create a new task #1 for project Management.
I copy the start date cell of the project summary task (Task #0), right click on the cell and select Copy Cell.
On the paste special window, select Paste Link and Text Data, and then click on OK.
Note that the bottom right corner of the cell contains this grey triangle which means that the cell is linked to something.
This is my simple project file
I will create a new task #1 for project Management.
I copy the start date cell of the project summary task (Task #0), right click on the cell and select Copy Cell.
Then the idea is to paste this cell as a link on the Project Management task start date. Right click on the cell and click Paste Special.
On the paste special window, select Paste Link and Text Data, and then click on OK.
Note that the bottom right corner of the cell contains this grey triangle which means that the cell is linked to something.
Repeat this for the End Date.
If we now open the Task Inspector for this task, we see that there is a Finish No Earlier Than constraint type.
Let’s modify another task, for example let’s put 5 days for task 1’s duration.
Note that the Project Management task has been extended until the new project end date.
How to synchronize Microsoft Project file with SharePoint task list
This post will show you how you can synchronize tasks from your Microsoft Project planning with SharePoint. This is typically addressed to users/companies that do not want to invest in the full version of Project server, but can share task list and project information with some stakeholders.
Create a Microsoft Project simple project
Here is a very simple project that contains 2 summary tasks with 3 subtasks for each of them.
Create a Microsoft Project simple project
Here is a very simple project that contains 2 summary tasks with 3 subtasks for each of them.
Create a task list in SharePoint
Open your SharePoint site, go on view all site content and click on Create.
Add Pinned Site feature on your website
This post will show you how to use the Internet Explorer 9 / Windows 7 feature of pinned sites. If you don’t know what “Pinned Site” feature is, check this video.
1. Create a new ASP.NET Application project in Visual Studio
2. Create 2 additional pages and a folder Images that will contains 3 icons
3. Parameterize the button
5 Meta elements will help you to parameterize your button:
Element name
|
Value
|
Example
|
application-name
|
Name of the button. If no name is specified, the default name will be the name of the web page.
|
<meta name="application-name" runat="server" content="My Application Name title" />
|
msapplication-tooltip
|
Tooltip displayed on mouse over the pinned site
|
<meta name="msapplication-tooltip" runat="server" content="This is the application ToolTip" />
|
msapplication-starturl
|
Relative or absolute URL of the page opened upon button click. By default it’s the page that was opened when site was pinned
|
<meta name="msapplication-starturl" content="/" />
|
msapplication-navbutton-color
|
Color of Back and Forward buttons
|
<meta name="msapplication-navbutton-color" content="#FF0000" />
|
msapplication-window
|
Size of the window that will be opened. If user resizes it, those values are overridden.
|
<meta name="msapplication-window" content="width=800;height=600" />
|
4. Parameterize task list
Only one Meta element is needed to define a task. This could contain 3 properties:
- Task name
- Web page URL
- Icon to display
<meta name="msapplication-task" content="name=Task number 1;action-uri=/Feature1.aspx;icon-uri=/Images/Icon1.ico"/>
5. Result
Here is the result for our small test.
<link rel="shortcut icon" type="image/x-icon" href="/Images/myIcon.ico" />
<link rel="icon" type="image/ico" href="/Images/myIcon.ico" />
<meta name="application-name" runat="server" content="My Application Name title" />
<meta name="msapplication-tooltip" runat="server" content="This is the application ToolTip" />
<meta name="msapplication-starturl" content="/" />
<meta name="msapplication-navbutton-color" content="#FF0000" />
<meta name="msapplication-window" content="width=800;height=600" />
<meta name="msapplication-task" content="name=Task number 1;action-uri=/Feature1.aspx;icon-uri=/Images/Icon1.ico"/>
<meta name="msapplication-task" content="name=Task number 2;action-uri=/feature2.aspx;icon-uri=/Images/icon2.ico"/>
Note that the list of task can be get from external source as config file (sitemap, …) or database
6. Open the site home page, the fav icon is displayed
7. Drag the tab of the site to the task bar to pin the site
8. Site is now pinned to task bar
9. Right click on the icon to discover the list of available tasks for this site as we have defined
On button mouse over, you see the site name
10. The Back and Forward buttons are colored as defined (#FF0000 = red)
11. There is also another feature that allows you to add the site to the Start Menu.
12. Site and its tasks are now available from the start menu.
13. Facebook is a good example as pinned site, try to pin Facebook site to discover available tasks from pinned site button
How to reduce Excel file size using a Macro
I found this VBA script to reduce Excel file size; in my case it reduces the original Excel file from 59.9 MB to 40 KB… What a shrink J
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim myLastRow As Long
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range
Dim myLastCol As Long
Dim wks As Worksheet
Dim dummyRng As Range
For Each wks In ActiveWorkbook.Worksheets
With wks
myLastRow = 0
myLastCol = 0
Set dummyRng = .UsedRange
On Error Resume Next
myLastRow = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows).Row
myLastCol = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByColumns).Column
On Error GoTo 0
With wks
myLastRow = 0
myLastCol = 0
Set dummyRng = .UsedRange
On Error Resume Next
myLastRow = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByRows).Row
myLastCol = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchdirection:=xlPrevious, _
searchorder:=xlByColumns).Column
On Error GoTo 0
If myLastRow * myLastCol = 0 Then
.Columns.Delete
Else
.Range(.Cells(myLastRow + 1, 1), _
.Cells(.Rows.Count, 1)).EntireRow.Delete
.Range(.Cells(1, myLastCol + 1), _
.Cells(1, .Columns.Count)).EntireColumn.Delete
End If
End With
Next wks
.Columns.Delete
Else
.Range(.Cells(myLastRow + 1, 1), _
.Cells(.Rows.Count, 1)).EntireRow.Delete
.Range(.Cells(1, myLastCol + 1), _
.Cells(1, .Columns.Count)).EntireColumn.Delete
End If
End With
Next wks
End Sub
Using Traffic Lights in Project reporting
Following my previous article on Earned Value Management (Project Management: Understanding Earned Value Management), here is a very simple technique that probably many of us are already using, which helps to clearly define project health on an easy way.
Traffic Lights definition and usage
These indicators are generally used in summary reporting (the status of activities within a project), or in program reporting (the status of projects within a program). They are particularly useful for giving management focus on activities or projects which require particular attention.
We sometimes use the term RAG for these indicators, stating for Red / Amber / Green.
The definitions of RAG indicators are agreed at the beginning of the project and used in reports and presentations regarding its status.
Examples
The project team has agreed to define following boundaries on Cost Performance Indicators. (Remember that CPI = EV / AC) The “traffic light” indicators could then be defined as:
- Red if CPI < 80%
- Amber if 80% ≤ CPI ≤ 95%
- Green if CPI > 95%
Usage in Microsoft Project® (Project plan)
Here is an example of using traffic lights inside Microsoft Project®.
How can you implement such indicators in Microsoft Project ®?
- Create a new custom field for task, with type: Number,
- Select Custom attribute: Formula,
- Enter the following formula,
The result is:
- 1 if SPI >=0.9,
- 2 if 0.8=<SPI<0.9,
- 3 if SPI<0.8
- Select display graphical indicators,
- Choose your graphical indicators.
Usage in Microsoft Office Excel (Project report)
Another example of traffic lights usage, an issue log in Excel, I have used the conditional formatting to color cells.
Here are the rules used for the conditional formatting.
Usage in Microsoft Office PowerPoint (Steering committee presentation)
The last example I wanted to show is the usage of traffic lights in PowerPoint presentations. Here is a print screen of one of my sides regarding my project cost status.
Overall project cost indicator is on top, and a more detailed presentation for Capex / expenses is given below.
Advantages of such technique
- It is very intuitive and visual,
- And very effective for summary reporting, or management presentations.
You should pay attention on following points
- The values of the indicators must be agreed at the beginning of the project, and should be consistent with other projects
- The summary nature of the indicators can be misleading (i.e. how “green” is an activity, what is the trend?)
- They do not provide sufficient information to allow diagnosis and interpretation, without discussion.
Consequences and actions
- Maintain the performance of projects/activities which are “green”,
- Diagnose the risks and issues of projects/activities which are “amber” and take appropriate action to remedy problems,
- Diagnose and remedy issues with projects/activities which are “red”, seeking management support and endorsement as appropriate.
Hide SharePoint 2007 list column from edition using JavaScript
Here is a quite simple way to hide some column in a SharePoint list when you create/edit/view a list item, using JavaScript.
Here is my list definition:
I just have Title and MyHiddenColumn columns.
Here are the 4 pages where my list can be viewed and edited.
Create new item:
List view:
Display item:
Edit item:
What I want to do is to hide the MyHiddenColumn column (this could be useful to manage confidential information related to a list item).
To hide this column to the list view, I just have to remove the column from the view (easy).
To hide the column on the 3 other screens, I will use the following JavaScript code, included in the page:
<script language="javascript" type="text/javascript">
_spBodyOnLoadFunctionNames.push("hideFields");
function findacontrol(FieldName) {
var arr = document.getElementsByTagName("!");
// get all comments
for (var i=0;i < arr.length; i++ )
{
// now match the field name
if (arr[i].innerHTML.indexOf(FieldName) > 0) {
return arr[i];
}
}
}
function hideFields() {
var control = findacontrol("MyHiddenColumn");
control.parentNode.parentNode.style.display="none";
}
</script>
Where MyHiddenColumn is the name of the column I want to hide.
You can edit the page using Microsoft Office SharePoint Designer.
Save your change.
The result is then:
Create new item:
Display item:
Edit item:
Not so stupid question now, how can I edit this hidden field? The solution is Data Sheet view:
If you ensure that standard users cannot use another view than the default one (without this column displayed), this is a simple solution to add confidential information to a list item without creating a second list.
Subscribe to:
Posts
(
Atom
)
