Pulling Percentage values between sheets and identifying Name fields

[Murtaza-Talib]

Hello,

I am creating an Employee Utilization Excel for my organization. In the first sheet, I will be keeping all the names of the Employees. In the other sheet, I will keep the same names BUT assigned under clients.

What I want to do, is that after I assign each employee under a particular client, I will also assign a percentage value of his/her utilization on that Client. As I do this, I want a way for this to add up and reflect on the First sheet under that particular person's name.

For e.g., If I assign Person A to 50% under client A, and 25% under Client B, then in the first sheet, Person A's total utilization should show as 75%.

I can do this manually, which is painstaking, so is there a Formula I can use. Every time I assign a new client to Person A, his total utilization in the first sheet should change.

[CARACALLA]

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen

[Murtaza-Talib]

Here is the attachement

[JohnTopley]

Your layout does not lend itself to easy use of formula with blank spaces everywhere. I strongly recommend you change it to a straightforward row/column format.

[JohnTopley]

See attached Sheet1:

You will there is the same basic formula but adjusted for the various client ranges. You could (should!) format your table like this and the only formula required is the SUM in column E

The cells are formatted with 0.00%;-0;;@ to hide the zero values

[Murtaza-Talib]

Thank you so much, John. It works amazing. I will just finish it off my adding the rest of my clients and employees. and following the Sheet you made.

Appreciate your help.

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.