Sorting figures/Codes Dynamically?

I’m attempting to automate a payroll system and so far have made decent progress. I’d like to finish this up if I can and have it bridged in excel. I have a sample attached of what I’m working on and thinks to the help of this board and a lot of help from one person in particular!!! I was able to get it up and running to this point the only thing I’m missing is…

Example of what I’d like to do:

Employee 101, John.. on “Copy Totals” I’d like excel to be able to total up all of Johns hours in column K 24.75, 168 and 48 then put that total into “Dump Totals Here” U2 and have it minus off 106. 24.75+168+48-106=134.75.. easy enough you think.. and it is apart from one thing.. the totals for John are static now but next week he may not use 48 hours of vacation instead he may now have 2 sets of figures or he may have one listed in K. I need to find a way for excel to search the entire “Copy Totals” sheet and know which employee is John, 101 and which is Bob 102.. etc and where each of their totals are, where one starts and the other ends. Figures in “Copy Totals” will change as it’s a payroll report for people and will change week by week. Basically where you see any numbers highlighted in red in the “Dump Totals Here” worksheet they would need to be fed from the “Copy Totals” worksheet.

Where each total should go or if it should be ignored is listed in the “Codes” worksheet.

The next thing I need to do assuming we can get the above to work is for excel to know the difference between the employees and then search within each employee for a code for example John, 101 M & N 8 is VA (which means vacation) the total for VA is held in K8. Take that total then place it for John into “Dump Totals Here” into the correct column e.g. VA would drop into H2. Again easy enough as this is static but next week vacation may not be in the same row although it’ll still be in the same columns M & N.

What I’m trying to do is automate this process so I can have one excel sheet collect all the figures it needs from the other then dump them where needed. The “Dump Totals Here” should be able to do everything I need it to do I just need it to automatically collect that information from “Copy Totals” and dump everything in the correct area.

I made the codes worksheet to try and explain where I’d like each code to be put or the figure for each code to appear?

There is one more part I need to get working but I think I’ll save that for later! I am reading through the forum!

Thanks,

Try using the sumif formula. E.g. =Sumif(yourrange,"JohnVA",yoursumrange)
You will need to add text together to create your condition.
e.g. ="John"&"VA"

Because the columns are offset you just need to use an if statement.
E.g. If(Sheet1!B3="",B2,Sheet1!B3)


see attached for example.

A few days or a few seconds?

You can generate the 5600 entries very quickly though. You just need to have the codes in a row and the names in a column then you can use the concatenate formula or the "&" to add the text together to create the Name&Code. Then use the sumif formula =Sumif(criteriarange,"name"&"code",sumrange)
Then fix the references using $ in the appropriate places and drag the formula across all the cells. This is dynamic so if you want to change a name no problem.

...in fact you could do this straight into your "Dump Totals Here" sheet

see attached

P.S. If you want to have a master table (in sheet1) you could have a lookup to it in your Dump Totals... use the 'vlookup' or 'index' formula and the 'match' formula for a 2D lookup. e.g. =vlookup("James",A1:Z100,match("OT",A1:Z1,false),false)