# Lookup codes and names and add costs

#### HMN93

##### New Member
Hi everyone,

I would appreciate it if someone can help me with this.
I have a workbook of 13 sheets. All 13 sheets contain the same information:Activity-Employee Code-Employee Name-Code1-Cost1-Code2-Cost2.
I'll show a small example to illustrate my case:
I have a table containing all Employees' Name and code
 Employee Name Employee Code Mark 900 Joe 901 Sally 902 Sam 903 Jesse 904 Ally 905 D.O 800 Liam 801 Al 802

<tbody>
</tbody>

This Table is in Sheet1 of the same workbook.

Now Sheet2 Contains the following:
 Activity Employee Code Employee Name Code1 Cost1 Code2 Codet2 A Mark 900 1020 \$ 10 1020 \$ 15 A-105 Joe 901 1010 \$ 20 1010 \$ 18 A-S05 Al 802 1020 \$ 15 1010 \$ 20 B-106 D.O 800 1040 \$ 18 1020 \$ 15 B-S06 Jesse 94 1020 \$ 20 1040 \$ 20

<tbody>
</tbody>

What I need to happen in this sheet is to extract all codes with the corresponding Employee Name and cost.
Let's Take for example Code 1020:
I need to have the following:
 Employee Name Cost1 Mark \$ 10 Al \$ 15 Jesse \$ 20

<tbody>
</tbody>

 Employee Name Cost2 Mark \$ 15 D.O \$ 15

<tbody>
</tbody>

And then Having the Total Summary Table for both Cost (Cost1+Cost2)
 Employee Name Total Cost Mark \$ 25 Al \$ 15 Jesse \$ 20 D.O \$ 15

<tbody>
</tbody>

By this the data for Sheet2 will be done.

Now if I jump to Sheet3 and do the same procedure, I'll end up with this total summary table :
 Employee Name Total Cost Sally \$ 35 Sam \$ 10 Mark \$ 17 Jesse \$ 20

<tbody>
</tbody>

Now the final table that I need to reach is a table that sum all costs for same employee across multiple sheets for a specific Code.
The Final Table of the example shown above would be like this:
 Code 1020 Employee Name Cost Mark \$ 42 Sally \$ 35 Sam \$ 10 Jesse \$ 40 D.O \$ 15 Al \$ 20

<tbody>
</tbody>

I need to do this using formula and not filter. I would appreciate it someone can help with this!

#### mole999

##### Moderator
are you not allowed to use pivot tables

#### HMN93

##### New Member
No i am not allowed to. I solved one part of this by using index but after that I didn't know how to proceed.

#### mole999

##### Moderator
No i am not allowed to. I solved one part of this by using index but after that I didn't know how to proceed.
so its an exam type question ?

#### HMN93

##### New Member
well not an exam it's part of a project

1,082,344
Messages
5,364,806
Members
400,814
Latest member
gangstar67

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...