none
Project 2013 Custom Number Field Roll Up but exclude all milestones RRS feed

  • Question

  • If this has been answered before, I apologize.  

    I have a custom number field in Project 2013.  I need this field to roll up in the summary task but exclude any milestone fields (similar to % Complete).  This seems like a simple straight forward action.  However, I can't find a solution.  

    So, if I have 4 sub tasks where the 4th task is the milestone, average the 3 other sub tasks.  

    I'm new to custom  fields and would appreciate any help.

    Thanks!

    Monday, July 7, 2014 11:04 PM

All replies

  • sdpierce,

    Just for reference the % Complete field at summary level is not a roll up average.

    In order to do what I think you want, it will take at least 3 custom number fields (e.g. Number1, Number2, Number3) with these formulas:

    Number1=IIf([milestone],0,{whatever your custom number formula})

    Number2=IIf([milestone],0,1)

    Number3=[Number1]/[Number2]

    For the Number1 and Number2 fields, roll up a sum value to the summary line. For the Number3 field, use the formula at summary level.

    Not quite as easy as you had hoped I'm sure. However, this could be readily done with some VBA, as follows (assuming your custom field is the Number1 field and there is no rollup for task summary lines):

    Sub AveWithoutMile()
    Dim t As Task
    Dim st As Task
    Dim SumVal As Single
    Dim Div As Integer
    For Each t In ActiveProject.Tasks
        If t.Summary Then
            For Each st In t.OutlineChildren
                If Not st.Milestone Then
                    SumVal = SumVal + st.Number1
                    Div = Div + 1
                End If
            Next st
            t.Number1 = SumVal / Div
            SumVal = 0: Div = 0
        End If
    Next t
            
    End Sub

    John



    Monday, July 7, 2014 11:49 PM
  • Thanks for the quick response, John.  Would the VBA need to be "macro'd" or could it trigger by an event?  In other words, what causes the code to run?

    Thanks

    Tuesday, July 8, 2014 11:00 AM
  • sdpierce,

    I'm not sure what you mean by "marco'd" but it could be run a couple of different ways. First, and probably the most common way, is to initiate it via user action. In my case, for macros I wish to run on a regular basis, I set up a custom tab on the ribbon with a button (icon) that runs the macro. It could also be added to the quick access toolbar. Second, the macro can be triggered by an event (e.g. file open, close, or other event). It's pretty straightforward to set it up for open or close events but gets a little more complex to set it up for other events.

    If this answers your question or is as least helpful, please vote or mark as the answer.

    John

    Tuesday, July 8, 2014 2:43 PM
  • Hi John,

    I would like for the code to run automatically when I update the field.  I'm researching different event handlers now.  

    Also, it looks like the code works for a schedule 2 levels deep.  Thank you.  

    However, if the schedule is several levels deep, I need for the values to roll up all the way to the top summary task.  Any thoughts?

    Thanks!

    Tuesday, July 8, 2014 5:57 PM
  • sdpierce,

    Which field are you updating? The Change Event is probably the most appropriate, but firing on the Change Event can sometimes cause a macro to run when you don't intend it to run. For example, let's say you are editing and change a field, (not the field you are updating), that ripples through several fields and ends up changing the field you updated. This may not be a problem in your case, but it's worth considering. My preference is to fire on the Open or Close events.

    With regard to levels deep, the macro actually will cover all outline levels, but as written you would need to run it as many times as you have levels (I wrote it as a quick approach, the full intent of what you wanted wasn't specified in your original post). For multiple levels and full rollup, the code needs to be modified to calculate the highest outline level first (e.g. outline level 10) and then continue on down through to the lowest level ( i.e. outline level 1).

    John

    Tuesday, July 8, 2014 6:36 PM
  • Hi John,

    I wanted to update that field in all of the summary rows affected by that field update. 

    I was experimenting with the change event and it appeared to be the one I needed.  

    Open or Close doesn't give me the dynamic update that the users are accustomed to seeing in Project.

    I, now, have other priorities so this issue is tabled for now.  However, you got me started and that was the kick I needed.  Thank you for your help!


    Friday, July 11, 2014 6:21 PM
  • sdpierce,

    You're welcome and thanks for the feedback.

    For help in setting up the Change Event, take a look at Jack Dahlgren's website at: http://masamiki.com/project/macros.htm

    If this answered your question or was at least helpful, please mark as the answer or vote.

    John

    Friday, July 11, 2014 8:37 PM