none
Looking for a formula in Resource Usage view text field to copy 1st 7 digits of data from assignment field to a text field RRS feed

  • Question

  • Hello

    We are using MS Project Professional 2007. CSR = Change system request

    In the Task name on the Gannt chart view IF the task is a CSR we are filling in CSR numbers in the first 7 characters of each task as such.... CSR 136, CSR 599, etc.

    The resource usage view shows the assignments per resource of which the first 7 characters is CSR nnn.

    In the resource usage view and for each assignment of that resource I would like a formula to copy the first 7 characters of that assignment ( I guess resource name column) into text3 ONLY if the first 3 characters are CSR.

    What would the formula be and would placing the formula in the Text 3 customize area give me what I want.

    ....or how would I do that?

    I am actually trying to group all the resource names by CSR number

    Thanks

     

    On the Resource Usage view, I would like to take Text 3 and copy the first 7 characters of resource name into Test3 using a formula.

    Can someone tell mew what that formual would be in teh customize

     

    I am looking for a formula to place

    Saturday, August 13, 2011 4:19 AM

Answers

  • Well, I modified the code and tested. It works. Thanks to Jack !!!

    If you decide to go with VBA, insert the following modified routine into a Module and try it in backup copy of your original schedule.

    Sub CopyToAssignment()
    Dim R  As Resource
    Dim RS As Resources
    Dim A  As Assignment

    Set RS = ActiveProject.Resources

    For Each R In RS
        If Not R Is Nothing Then
            For Each A In R.Assignments
                If Left(A.TaskName, 3) = "CSR" Then
                   A.Text3 = Left(A.TaskName, 7)
                End If
            Next A
        End If
    Next R
    End Sub


    Regards. --Ismet
    Saturday, August 13, 2011 6:48 PM
  • Hotmail1,

    The following code should do it (if I understand correctly what you want). I've tweaked the original code to streamline it a bit and make it more robust.

     

    Option Explicit

    Option Compare Text

    Sub CopyCSRCode()

    Dim R  As Resource

    Dim t As Task

    Dim A  As Assignment

    For Each R In ActiveProject.Resources

        If Not R Is Nothing Then

            For Each A In R.Assignments

                If Left(A.TaskName, 3) = "CSR" Then

                   A.Text3 = Left(A.TaskName, 7)

                End If

            Next A

        End If

    Next R

    For Each t In ActiveProject.Tasks

        If Not t Is Nothing Then

            If Left(t.Name, 3) = "CSR" Then

                t.Text4 = Left(t.Name, 7)

            End If

        End If

    Next t

    End Sub

     

     

    John
    Saturday, August 20, 2011 3:39 PM

All replies

  • The formula IIf(Left([Name],3)="CSR",Left([Name],7),"") may work in Task Usage view. (Sorry for a lot of editing!)

    Regards. --Ismet

    Saturday, August 13, 2011 4:51 AM
  • In the resource usage view and for each assignment of that resource I would like a formula to copy the first 7 characters of that assignment ( I guess resource name column) into text3 ONLY if the first 3 characters are CSR.

    It seems relatively easy to copy/paste Name column of Resource Usage view from Project to Excel and to handle the assignments with CSR string there. Inserting Assignment field (Yes/No) to Resource Usage view and including it in copy/paste will help to decide whether the line is an assignment line or not while working in Excel.

    Regards. --Ismet
    Saturday, August 13, 2011 5:16 AM
  • Resource Usage View:

     

    Resource Name   Assignment

    Resource1         No

      CSR12345        Yes

    Resource2         No

      CSR54378        Yes

    Resource3         No

      CSR12345        Yes

      CSR54378        Yes

     

    Excel Sheet:

     

    Column A   Column B   Column C

    Resource1         No    

    CSR12345          Yes   CSR1234

    Resource2         No    

    CSR54378          Yes   CSR5437

    Resource3         No    

    CSR12345          Yes   CSR1234

    CSR54378          Yes   CSR5437

     

    Formula in Column C is =IF(AND(B1="Yes", LEFT(A1,3)="CSR"), LEFT(A1,7),"")



    Saturday, August 13, 2011 5:35 AM
  • I do want to use this in MS Project and NOT excel

    Also, I would like to use Resource Usage view like I indicated.

    I placed that formula in Text 3 in the Resource Usage view and it didn't work. It shows blanks

    What am I doing wrong

    Saturday, August 13, 2011 12:33 PM
  • You cannot create custom assignment fields in Project, that's by design. Formulas will work for tasks and resources but not for assignments in Usage views. That's the reason why I used Task Usage view or Excel. There is a discussion on the same topic here at: http://www.eggheadcafe.com/software/aspnet/29902470/custom-assignment-fields.aspx

    It is possible by using VBA. Check "Copy Task Fields" macro by Jack Dahlgren here at: http://masamiki.com/project/macros.htm

    Needs modification to use in Resource Usage view. Pls always work with backup copies of your original schedule while doing code trials.

     


    Regards. --Ismet




    Saturday, August 13, 2011 4:37 PM
  • Well, I modified the code and tested. It works. Thanks to Jack !!!

    If you decide to go with VBA, insert the following modified routine into a Module and try it in backup copy of your original schedule.

    Sub CopyToAssignment()
    Dim R  As Resource
    Dim RS As Resources
    Dim A  As Assignment

    Set RS = ActiveProject.Resources

    For Each R In RS
        If Not R Is Nothing Then
            For Each A In R.Assignments
                If Left(A.TaskName, 3) = "CSR" Then
                   A.Text3 = Left(A.TaskName, 7)
                End If
            Next A
        End If
    Next R
    End Sub


    Regards. --Ismet
    Saturday, August 13, 2011 6:48 PM
  • Ismet

     

    This works great. Thank You.

    If I wanted to also populate Text4 on the Gantt view at the same time. What would be the additional code for that within the code provided.

     

    Thanks

    Saturday, August 20, 2011 3:15 PM
  • Hotmail1,

    The following code should do it (if I understand correctly what you want). I've tweaked the original code to streamline it a bit and make it more robust.

     

    Option Explicit

    Option Compare Text

    Sub CopyCSRCode()

    Dim R  As Resource

    Dim t As Task

    Dim A  As Assignment

    For Each R In ActiveProject.Resources

        If Not R Is Nothing Then

            For Each A In R.Assignments

                If Left(A.TaskName, 3) = "CSR" Then

                   A.Text3 = Left(A.TaskName, 7)

                End If

            Next A

        End If

    Next R

    For Each t In ActiveProject.Tasks

        If Not t Is Nothing Then

            If Left(t.Name, 3) = "CSR" Then

                t.Text4 = Left(t.Name, 7)

            End If

        End If

    Next t

    End Sub

     

     

    John
    Saturday, August 20, 2011 3:39 PM
  • You're welcome. I guess John's already answered Text4 part of your question.
    Regards. --Ismet
    Sunday, August 21, 2011 8:46 PM