Lookup codes and names and add costs

[HMN93]

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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Employee Name[/TD]
[TD]Employee Code
[/TD]
[/TR]
[TR]
[TD]Mark[/TD]
[TD]900[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]901[/TD]
[/TR]
[TR]
[TD]Sally[/TD]
[TD]902[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]903[/TD]
[/TR]
[TR]
[TD]Jesse[/TD]
[TD]904[/TD]
[/TR]
[TR]
[TD]Ally[/TD]
[TD]905[/TD]
[/TR]
[TR]
[TD]D.O[/TD]
[TD]800[/TD]
[/TR]
[TR]
[TD]Liam[/TD]
[TD]801[/TD]
[/TR]
[TR]
[TD]Al[/TD]
[TD]802[/TD]
[/TR]
</tbody>[/TABLE]

This Table is in Sheet1 of the same workbook.

Now Sheet2 Contains the following:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Activity[/TD]
[TD]Employee Code[/TD]
[TD]Employee Name[/TD]
[TD]Code1[/TD]
[TD]Cost1[/TD]
[TD]Code2[/TD]
[TD]Codet2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Mark[/TD]
[TD]900[/TD]
[TD]1020[/TD]
[TD]$ 10[/TD]
[TD]1020[/TD]
[TD]$ 15[/TD]
[/TR]
[TR]
[TD]A-105[/TD]
[TD]Joe[/TD]
[TD]901[/TD]
[TD]1010[/TD]
[TD]$ 20[/TD]
[TD]1010[/TD]
[TD]$ 18[/TD]
[/TR]
[TR]
[TD]A-S05[/TD]
[TD]Al[/TD]
[TD]802[/TD]
[TD]1020[/TD]
[TD]$ 15[/TD]
[TD]1010[/TD]
[TD]$ 20[/TD]
[/TR]
[TR]
[TD]B-106[/TD]
[TD]D.O[/TD]
[TD]800[/TD]
[TD]1040[/TD]
[TD]$ 18[/TD]
[TD]1020[/TD]
[TD]$ 15[/TD]
[/TR]
[TR]
[TD]B-S06[/TD]
[TD]Jesse[/TD]
[TD]94[/TD]
[TD]1020[/TD]
[TD]$ 20[/TD]
[TD]1040[/TD]
[TD]$ 20[/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Employee Name[/TD]
[TD]Cost1[/TD]
[/TR]
[TR]
[TD]Mark[/TD]
[TD]$ 10[/TD]
[/TR]
[TR]
[TD]Al[/TD]
[TD]$ 15[/TD]
[/TR]
[TR]
[TD]Jesse[/TD]
[TD]$ 20[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Employee Name[/TD]
[TD]Cost2[/TD]
[/TR]
[TR]
[TD]Mark[/TD]
[TD]$ 15[/TD]
[/TR]
[TR]
[TD]D.O[/TD]
[TD]$ 15[/TD]
[/TR]
</tbody>[/TABLE]

And then Having the Total Summary Table for both Cost (Cost1+Cost2)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Employee Name[/TD]
[TD]Total Cost[/TD]
[/TR]
[TR]
[TD]Mark[/TD]
[TD]$ 25[/TD]
[/TR]
[TR]
[TD]Al[/TD]
[TD]$ 15[/TD]
[/TR]
[TR]
[TD]Jesse[/TD]
[TD]$ 20[/TD]
[/TR]
[TR]
[TD]D.O[/TD]
[TD]$ 15[/TD]
[/TR]
</tbody>[/TABLE]

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 :
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Employee Name[/TD]
[TD]Total Cost[/TD]
[/TR]
[TR]
[TD]Sally[/TD]
[TD]$ 35[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]$ 10[/TD]
[/TR]
[TR]
[TD]Mark[/TD]
[TD]$ 17[/TD]
[/TR]
[TR]
[TD]Jesse[/TD]
[TD]$ 20[/TD]
[/TR]
</tbody>[/TABLE]


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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Code[/TD]
[TD]1020[/TD]
[/TR]
[TR]
[TD]Employee Name[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]Mark[/TD]
[TD]$ 42[/TD]
[/TR]
[TR]
[TD]Sally[/TD]
[TD]$ 35[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]$ 10[/TD]
[/TR]
[TR]
[TD]Jesse[/TD]
[TD]$ 40[/TD]
[/TR]
[TR]
[TD]D.O[/TD]
[TD]$ 15[/TD]
[/TR]
[TR]
[TD]Al[/TD]
[TD]$ 20[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[mole999]

are you not allowed to use pivot tables

 

[HMN93]

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]

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]

well not an exam it's part of a project