Determine Resource Allocations by evaluating available time and required time.
 

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!!!!

Hi,

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

Hello!

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

Thanks for any help!

Determine Resource Allocations by evaluating available time andrequired time.
 

On Thursday, April 5, 2012 2:10:43 PM UTC-5, DieselDork wrote:
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|
+-------------------------------------------------------------------+



--
DieselDork

How about a couple of AFTER examples and the logic of how you get from 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 leads availability
6. the project selects a platform resource and steps 2-5 are performed to reserve the platform resource.
7. same for network.
8 same for storage.

Hopefully that's helpful.