• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamically calculating the values and populating them on the dashboard in Excel

M

msfromdelhi

New Member
I trying to build the resource capacity template, in which I have got 3 sheets

  1. Capacity
  2. Demand
  3. Dashboard

Capacity Sheet Sample Data:

RESOURCE NAMESKILL GROUPPROJECTCOST PER HOURCAPACITY
Resource 1ManagementProject 112
960​
Resource 2FinananceProject 211
937.5​
Resource 3SalesProject 412
480​
Resource 4SupportProject 312
1072.5​
Resource 5TestingProject 111
922.5​
Resource 6ManagementProject 212
900​
Resource 7FinananceProject 310
810​
Resource 8TestingProject 412
960​
Resource 9SalesProject 111
172.5​
Resource 10SupportProject 211
172.5​
Resource 11TestingProject 412
900​

Demand Sheet Sample Data:
RESOURCE NAMESKILL GROUPPROJECTDEMAND
Resource 1ManagementProject 1
400​
Resource 2FinananceProject 2
300​
Resource 1SalesProject 4
250​
Resource 2SupportProject 2
120​
Resource 1TestingProject 1
540​
Resource 3ManagementProject 2
430​
Resource 3FinananceProject 3
232​
Resource 1TestingProject 3
415​
Resource 2SalesProject 1
40​
Resource 4SupportProject 3
60​
Resource 4TestingProject 4
400​

Dashboard Sheet Sample Data:
DEFICITPROJECTSSURPLUS
PROJECTCapacityDemandDemand-CapacityPROJECTCapacityDemandCapacity-Demand
SKILLS
SKILL GROUPCapacityDemandDemand-CapacitySKILL GROUPCapacityDemandCapacity-Demand

On the dashboard I would like to populate the projects, skills and resources on the basis of capacity and demand. Something like this for Project section.
DEFICITPROJECTSSURPLUS
PROJECTCapacityDemandDemand-CapacityPROJECTCapacityDemandCapacity-Demand
Project 39001100200Project 11200790410
Project 21111567544

  1. In projects section, I am trying to populate the data of projects which has surplus and deficit hours. In future the project count can grow up. Surplus projects will appear in right hand side and deficit will come to left hand side.
  2. In Skills section, I am trying populate the data of Skills on the basis of capacity and demand.
  3. In Resource section, I will populate the number of resources who are unutilized on right side and similarly overutilized on left side.
I tried lot to get these details and getting success for per record only, however not able to understand how to get the details of each sections in a formula, also, considering that in future the project, skills and resources will grow, then how to handle them on the dashboard with limited space.
 
msfromdelhi
Please, reread Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
especially How to get the Best Results at Chandoo.org
After that, You'll remember - what have You missed?
 
You must log in or register to reply here.
Back
Top