Excel Question
 

Hello freinds,

I have been struggling with this one for some time. Any
suggestions will be appreciated. This is a resource utilization report
that I want to use to track resources.

Here are the inputs - Resource "table", Planner "table" and Allocation
"table"
Resources
----------
Department X
Bob
Chris
Steve
Sue
Tim

Project Planner
----------------
Project Start Date End Date
Proj1 1/1/2004 2/1/2004
Proj2 1/1/2004 1/15/2004
Proj3 3/1/2004 5/1/2004
Proj4 3/1/2004 4/1/2004
Proj5 5/1/2004 12/1/2004

Allocation "table"
------------------
Project PL Utilization
Proj1 Bob 10%
Proj2 Chris 90%
Proj3 Steve 15%
Proj4 Bob 20%
Proj5 Tim 40%


These are the basic inputs that I have. I use the last one to allocate
resources. Now the summary report shown below is what I am trying to
get to.

Summary
-------

REPORT
Jan-04 Feb-04 Mar-04 Apr-04 May-04
Bob 10% 10% 10% 10% 0%
Chris 90% 0% 0% 0% 0%
Steve 0% 0% 15% 15% 15%
Sue 0% 0% 0% 0% 0%
Tim 0% 0% 0% 0% 15%


This is just an example. My actual sheet has a lot more projects and a
few more reqources. I was able to get it done using my rusty excel
skills - read lots of if statements. But at the end of it the file is
28 Megs!!!!

Could some one help me figure how to do this to fit into a file thats
more manageable in size? Can I use arrays in some manner? Thanks!

Al Ilone

Hi,

Send a copy of this question AND sample data from your
worksheet to:


- Mark
http://www.geocities.com/excelmarksway


-----Original Message-----
Hello freinds,

I have been struggling with this one for some time.
Any
suggestions will be appreciated. This is a resource
utilization report
that I want to use to track resources.

Here are the inputs - Resource "table", Planner "table"
and Allocation
"table"
Resources
----------
Department X
Bob
Chris
Steve
Sue
Tim

Project Planner
----------------
Project Start Date End Date
Proj1 1/1/2004 2/1/2004
Proj2 1/1/2004 1/15/2004
Proj3 3/1/2004 5/1/2004
Proj4 3/1/2004 4/1/2004
Proj5 5/1/2004 12/1/2004

Allocation "table"
------------------
Project PL Utilization
Proj1 Bob 10%
Proj2 Chris 90%
Proj3 Steve 15%
Proj4 Bob 20%
Proj5 Tim 40%


These are the basic inputs that I have. I use the last
one to allocate
resources. Now the summary report shown below is what I
am trying to
get to.

Summary
-------

REPORT
Jan-04 Feb-04 Mar-04 Apr-04 May-04
Bob 10% 10% 10% 10% 0%
Chris 90% 0% 0% 0% 0%
Steve 0% 0% 15% 15% 15%
Sue 0% 0% 0% 0% 0%
Tim 0% 0% 0% 0% 15%


This is just an example. My actual sheet has a lot more
projects and a
few more reqources. I was able to get it done using my
rusty excel
skills - read lots of if statements. But at the end of it
the file is
28 Megs!!!!

Could some one help me figure how to do this to fit into
a file thats
more manageable in size? Can I use arrays in some manner?
Thanks!

Al Ilone
.