Determine Resource Allocations by evaluating available time and required time.

[DieselDork]

Greetings!

I've finally run up against a wall, and need to plead for help.

What I have is this:

A list of 50 employees, and their available time (# of hour
consolidated monthly) for 12 months.

a b c d e f g h i j k l m
Bob Network 64 120 177 0 0 0 0 0 0 0 0 0


I have another worksheet that contains 50 projects and the required tim
for each project (# of hours consilidated monthly) for 12 months.

a b c d e f g h i j k l m
projectName 20 16 25 0 0 0 0 44 55 78 99

Each project will require 4 employees each with a different skillse
(Lead, Platform, Network, Storage).

What I need to do is find a Lead, Platform, Network and Storage employe
for each project.

I need to end up with a worksheet containing project names and each o
the 4 employee names that are available to work on that project.
a b c d e
ProjectName Bob Cindy Carla Steve

I've tried all manner of things to accomplish this, and have finall
given up.

I've attached a spreadsheet with sample data to better illustrate wha
I'm trying to accomplish.

Can anyone help with a series of formulas/functions?

THANK YOU!!!

+-------------------------------------------------------------------
|Filename: Proj Portfolio and Allocations 2012.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=323
+-------------------------------------------------------------------

 

[DieselDork]

Hi,

Do each of the engineers on the "Availability" tab have all four skil
sets, or are they restricted to only one or some?

Hello!

They can have any one of the skill sets. I neglected to mention tha
there are some hidden tabs in the sheet. One of the hidden tabs i
"allocations". The allocations tab contains each engineers primary an
secondary discipline. The "availability" tab is calculated from th
"allocations" tab. The allocations come from a report that I receiv
monthly.

Thanks for any help

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Don Guillett]

Greetings!

I've finally run up against a wall, and need to plead for help.

What I have is this:

A list of 50 employees, and their available time (# of hours
consolidated monthly) for 12 months.

a b c d e f g h i j k l m
Bob Network 64 120 177 0 0 0 0 0 0 0 0 0


I have another worksheet that contains 50 projects and the required time
for each project (# of hours consilidated monthly) for 12 months.

a b c d e f g h i j k l m
projectName 20 16 25 0 0 0 0 44 55 78 99

Each project will require 4 employees each with a different skillset
(Lead, Platform, Network, Storage).

What I need to do is find a Lead, Platform, Network and Storage employee
for each project.

I need to end up with a worksheet containing project names and each of
the 4 employee names that are available to work on that project.
a b c d e
ProjectName Bob Cindy Carla Steve

I've tried all manner of things to accomplish this, and have finally
given up.

I've attached a spreadsheet with sample data to better illustrate what
I'm trying to accomplish.

Can anyone help with a series of formulas/functions?

THANK YOU!!!!


+-------------------------------------------------------------------+
|Filename: Proj Portfolio and Allocations 2012.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=323|
+-------------------------------------------------------------------+

How about a couple of AFTER examples and the logic of how you get from here to there.

 

[DieselDork]

How about a couple of AFTER examples and the logic of how you get fro
here to there.

Here's the logic the best I can currently describe it:


1. the project selects a lead
2. then the project checks the leads availability
3. if not available the project searches for the next lead
4. when an available lead is selected the lead is assigned
5. the projects time requirements are subtracted from the lead
availability
6. the project selects a platform resource and steps 2-5 are performe
to reserve the platform resource.
7. same for network.
8 same for storage.

Hopefully that's helpful

+-------------------------------------------------------------------
+-------------------------------------------------------------------