CODEWITHSUNDEEP
formulaprojectproject-managementms-project
Sam
Sam

Reputation: 497

MS Project formula calculation returns inconsistent results

In MS Project Professional I have a custom field that returns the correct value...sometimes, no value at other times, and an #ERROR at still other times with no apparent rhyme or reason.

The goal: I need to capture the [Resource Names] field for use in an external application - easy enough - but when I have a fixed units task with limited resource units I need to exclude the "[##%]" portion of the name. Example: Sam[25%] but I need just, "Sam"

The formula: IIf(IsNumeric(InStr(1,[Resource Names],"[")),LEFT([Resource Names],Len([Resource Names])-5),[Resource Names])

The results are in summary:

Strangely enough, Summary Tasks show no value which is correct.

The need: can someone help me fix the formula? Or, should I just suck it up and manually delete the offending brackets and numbers?

Upvotes: 0

Views: 163

Answers (1)

Rachel Hettinger
Rachel Hettinger

Reputation: 8442

If you only ever have one resource assigned to a task, this formula will work: IIf(0=InStr(1,[Resource Names],"["),[Resource Names],Left([Resource Names],InStr(1,[Resource Names],"[")-1)).

However, building a formula to handle more than one resource would be extremely tedious with the limited functions available. In that case a macro to update the field would work much better:

Sub GetResourceNames()
    Dim t As Task
    For Each t In ActiveProject.Tasks
        Dim resList As String
        resList = vbNullString
        Dim a As Assignment
        For Each a In t.Assignments
            resList = resList & "," & a.Resource.Name
        Next a
        t.Text2 = Mid$(resList, 2)
    Next t
End Sub

Upvotes: 1

Related Questions