# Project 2013 Custom Number Field Roll Up but exclude all milestones

• ### 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:

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 SumVal As Single
Dim Div As Integer
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.

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