Sum values contained in record and multiply those values by the column's associated lookup

Hello everyone,

I'm not sure my title is the greatest for this problem, but I am unsure how to explain it in such a short sentence.

Basically, I would like to calculate the professional fees of certain roles, during certain phases of a project. In my sample file you will find two worksheets. The first Worksheet is Deliverables (this is the phases of the project) and the second Worksheet is Deliverable Summary (a summary of the fees associated with each phase (deliverable) of the project).

In the Deliverables worksheet, you will see that every Deliverable has a Tab Level of 1 in the column to the left of it. This is an indicator of phases. Between each tab level of 1, is a phase of the project. To the right of the Deliverable's you will find days worked by certain roles (Role 1, Role 2, Role 3, etc.) and their respective days worked under them. Please note that there are days worked in the Sub Deliverable and not the main Deliverable (the ones with a Tab Level of 1).

In the Deliverable Summary worksheet, you will see a table that I have summarized to the best of my ability.

Professional Services column is a list of all Phases in the Deliverables worksheet, to the right is the summation of the deliverable's Professional Days (Professional Days column). In the same worksheet you will find a table called Role's Daily Fee, this table contains the Role's fees.

I would like the professional fees column to calculate the total fees (Roles * Days) by Phase.

In the attached sample file, you can see my manual calculation in the Deliverable Summary worksheet - Professional Fees Column.

VBA or Worksheet Function?
If a formula isn't possible I would be okay with doing this via VBA Macro, but I'm not sure where to start there either! If this isn't clear, please don't hesitate to ask so that I may clarify.

Thank you very much,

FerJo,

Thank you for your help! I think I should have given more details about the Project. In my original Excel System those deliverables are copied in from a proposal and then formatted via macro to the tab level.

Also, I can't have the calculations on one page because of security reasons. We are using macro to make the resources worksheet (where my roles will be) very hidden! Your file does give me what I need when it comes to the calculations but I have to have them split up for these purposes.


Thanks,
Channing

FerJo,

Thank you very much for the deliverable day per phase calculations! They are very effective. I believe I left out something though. The way I have the roles set up in the first sheet(Deliverables) compared to the second sheet (new_Deliverable Summary), they follow a perfect order. Role 1 - 5.

In my first sheet (Deliverables), I do not get the benefit of this set up, for instance, my roles could be listed like so... Role 2 | Role 3 | Role 1 | Role 5 | Role 4


Is there a way to set this offset calculation to dynamically lookup these roles and then match them with their respective role fee?


Thanks,
Channing

That's what I'm talking about and we're getting much closer... Things are getting even more interesting, because they're wanting me to have more than one of the same role! (However, we can make this unique if we need to by saying (Role 5 (2)) and giving it the same role fee.

Another thing that I noticed I'm unable to do, is to leave out one of the roles.

For instance:

I need to be able to say that I have more than one role 5

and

I need to be able to say that in this project I will not be able to use role 3!

I've attached another sample file.



Thanks,
Channing

Basically I can't be forced to have the same number of roles in the deliverable tab as I do in the fee list.

1) The max number of roles / fees can vary, basically we have a list with Roles and their associated fees. Some roles can have the same fee amount. I'd like to be able to add and remove from this list as a I please (basically using it as a list)

Example:
Project Manager - $2000
Sponsor - $2000
Subject Matter Expert - $1800
Analyst - $1200
Analyst 1 - $1000
Analyst 2 - $800

2) I have set 16 columns to be used in the deliverable tab.


Thanks,
Channing

1) The max number of roles / fees can vary, basically we have a list with Roles and their associated fees. Some roles can have the same fee amount. I'd like to be able to add and remove from this list as a I please (basically using it as a list)

Example:
Project Manager - $2000
Sponsor - $2000
Subject Matter Expert - $1800
Analyst - $1200
Analyst 1 - $1000
Analyst 2 - $800

2) I have set 16 columns to be used in the deliverable tab.


Thanks,
Channing

From a user standpoint they request the ability to assign any role to the project, and have the summary pull data from the roles assigned. The users do not want to do anything (hiding), but to assign roles and then have those roles pull the correct number from the fees list.


Is there a way to build a helper column on the summary sheet that is populated based on what roles have been selected, then those values can reference the fees list? Would that help in keeping the number of roles on the deliverables equal to the fees list?

I hope that made sense :/

Thanks,
Channing

FerJo,

It works like a charm, sir! Thank you for all your patience and hard work. Is there a way to give you reputation or make the thread Solved?

Thanks,
Channing