Suggestion needed

[ChrisP]

We have 5 projects going on, so I've created 5 different worksheets to track
them. My boss would like an "admin" page. This should look at all the other
worksheets and add the employees time together, if over 100% then they would
have 0% for admin, if less than 100% then the admin would be 100-(sum of the
5 worksheets).

My problem is that one employee could show up on everyone of the projects or
on just a few or none at all. The complication is that the employee can be on
a project more than one time (once for Capital and once for Expense). I have
to add the time for the employee together (for all the worksheets and both
Capital and Expense). I'm at a loss. Any suggestions? Here's an example of
what I'm talking about:

Project 1
John Doe - Capital - 32%
John Doe - Expense - 25%

Project 2
John Doe - Capital 15%

Project 3
John Doe - Expense - 20%

Project 4
(John Doe doesn't work on this)

Project 5
(John Doe doesn't work on this)

I need to have the admin page look like this:
John Doe - 8%

Any suggestions are appreciated!
Thanks,
Chris

 

[tsides]

If your worksheets are named Project1, Project2, Project3, Project4
and Project5...
If your Resource names are in column A in all spreadsheets...
If Capital/Expense is in column B in all spreadsheets...
If the % is in column C in all spreadsheets...

If the Resource Names are in column A on the Admin worksheet...
If "John Doe" is in cell A2

Create a helper column in column B on the Admin tab. Set the formula
in B2 to:
=SUMIF(Project1!A:A,admin!A2,Project1!C:C)+SUMIF(Project2!A:A,admin!
A2,Project2!C:C)+SUMIF(Project3!A:A,admin!A2,Project3!C:C)
+SUMIF(Project4!A:A,admin!A2,Project4!C:C)+SUMIF(Project5!A:A,admin!
A2,Project5!C:C)

Then, in column C, you will place the formula that will give you the
amount of admin time:
=IF(B2>=1,0,1-B2)
Be prepared to replace the "1" in both places in this formula with
"100" depending on how you've formatted your % numbers.

Now copy the formula in B2 down the B column, and the formula in C2
down the C column.

If desired, hide column B on the Admin tab, as it is a helper column
and might be confusing.

 

[Barb Reinhardt]

For this example, I have the name in cell A2

A2: John Doe
B1: Project 1

Worksheet for Project 1 has name that matches B1

Assume that the Percentages are in B2:B10 and the names are in A1:A10 of the
Project sheet

=SUMPRODUCT(--(INDIRECT("'"&B$1&"'!$A1:$A10")=$A2),(INDIRECT("'"&B$1&"'!B1:B10")))

Iff in C1, you have "Project 2", D1 is Project 3, etc., you can copy over
your calculations.

I know it's complicated.

HTH,
Barb Reinhardt

 

[ChrisP]

That is an AWESOME idea!!! thank you so much!