Microsoft Project: summing data from a Task field into a Resource field?

Question

In the Task Sheet, I have a custom Numbers column called "Jellybeans". Each task has a resource attached, and some number of beans gets entered into the field. So you'd have something like

Task: let loose the fnord Resource: Alice Jellybeans: 5

Task: open the store Resource: Alice Jellybeans: 3

Task: TURN IT OFF Resource: Bob: Jellybeans: 4

Now, in the Resource Sheet, I create another custom number field, "Total Jellybeans". I want this field to sum all of the jellybeans attached to the resources in the Task Sheet. So you'd have

Resource: Alice Total Jellybeans: 8

Resource: Bob Total Jellybeans: 4

--I cannot figure out how to do this--. Tasks and Resources don't really seem to talk to each other like I'd expect, and I can't find a good how-to on formula building, because I THINK that's how I need to approach this. Is there a simple way to do this that I'm missing, or is it excessively complex?

This can't be done with a formula in a customized field because a formula can only access field information for each resource--not across resources and certainly not across task assignments. You'll need to use VBA to do this calculation. — Rachel Hettinger

Antti Kivivalli Antti Kivivalli · Accepted Answer · 2017-06-27T11:51:13

You can do this the Project way:

make sure your Custom Field sums up for Group Summary rows
Group your View (or create a Grouped View!) based on Resource

That works when there's only one resource per task. The shared tasks would get their own groups - and shared Jellybeans!

Or you can do it the VBA way:

as you can see, I'm assuming the Jellybean Custom Field is the field Number1 for both the Tasks and the Resources (two separate fields)

in this model all the Resources get all the Jellybeans but you can add calculation there

Sub SumUpJellybeans()

Dim vResource As Resource
Dim vAssignment As Assignment
Dim vJellybeans As Long

For Each vResource In ActiveProject.Resources

For Each vAssignment In vResource.Assignments

    vJellybeans = vJellybeans + vAssignment.Task.Number1

Next vAssignment

    vResource.Number1 = vJellybeans
    vJellybeans = 0

Next vResource

End Sub

Microsoft Project: summing data from a Task field into a Resource field?

1 Answers