Formula Help

[Pik]

I have the data mentioned below - two projects in a program, shared
resources across the projects.

Project Resource Week 1 Week 2 Week 3
-------- -------- ----------------------
Project 1 A 20 20 20
B 10 10 10



Project Resource Week 1 Week 2 Week 3
-------- -------- ----------------------
Project 2 B 25 20 10
C 40 40 40

I need to create a simple resource allocation summary, as shown below.

Resource Allocation Summary
Week 1 Week 2 Week 3
A 20 20 20
B 35 30 20
C 40 40 40

Basically, in the resource allocation summary section, I need (with
value 35 as an example), "Find resource B in Week 1 column of Project
1 and Project 2, and sum the hours spent".

Please help me out. Thanks.

 

[Jerry W. Lewis]

Assuming that
- Project 1 is on a sheet called "Project1", ...
- Resource is column B, Week 1 is column C, ...
- The first resource is in row 2, ...
- Your Summary is on a separate sheet with the same layout
- You have already entered the resources in column B

The following formula in row C2 of your summary should contain

=IF(ISNA(VLOOKUP($B2,Project1!$B$2:$E$3,COLUMN()-1,FALSE)),0,VLOOKUP($B2,Project1!$B$2:$E$3,COLUMN()-1,FALSE))+IF(ISNA(VLOOKUP($B2,Project2!$B$2:$E$3,COLUMN()-1,FALSE)),0,VLOOKUP($B2,Project2!$B$2:$E$3,COLUMN()-1,FALSE))

which you can copy down over the entire table

If you have more than 3 weeks, increase $E accordingly. If you have
more than 2 resources per project, increase $3 accordingly.

VLOOKUP will return #N/A if a given resource was not used on a given
project. That is why VLOOKUP must be wrapped in an IF function.

Jerry