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

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 NAME SKILL GROUP PROJECT COST PER HOUR CAPACITY Resource 1 Management Project 1 12 960​ Resource 2 Finanance Project 2 11 937.5​ Resource 3 Sales Project 4 12 480​ Resource 4 Support Project 3 12 1072.5​ Resource 5 Testing Project 1 11 922.5​ Resource 6 Management Project 2 12 900​ Resource 7 Finanance Project 3 10 810​ Resource 8 Testing Project 4 12 960​ Resource 9 Sales Project 1 11 172.5​ Resource 10 Support Project 2 11 172.5​ Resource 11 Testing Project 4 12 900​

Demand Sheet Sample Data:
 RESOURCE NAME SKILL GROUP PROJECT DEMAND Resource 1 Management Project 1 400​ Resource 2 Finanance Project 2 300​ Resource 1 Sales Project 4 250​ Resource 2 Support Project 2 120​ Resource 1 Testing Project 1 540​ Resource 3 Management Project 2 430​ Resource 3 Finanance Project 3 232​ Resource 1 Testing Project 3 415​ Resource 2 Sales Project 1 40​ Resource 4 Support Project 3 60​ Resource 4 Testing Project 4 400​

Dashboard Sheet Sample Data:
 DEFICIT PROJECTS SURPLUS PROJECT Capacity Demand Demand-Capacity PROJECT Capacity Demand Capacity-Demand SKILLS SKILL GROUP Capacity Demand Demand-Capacity SKILL GROUP Capacity Demand Capacity-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.
 DEFICIT PROJECTS SURPLUS PROJECT Capacity Demand Demand-Capacity PROJECT Capacity Demand Capacity-Demand Project 3 900 1100 200 Project 1 1200 790 410 Project 2 1111 567 544

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