Formula to distribute payroll?

[2709236]

I have weekly payroll which needs to be distributed across accounts and cost centres. Attached is a simplified sample.

The idea is that each week information would be dropped into the "Wk27" tab and also the "Production Data" tabs. I'd like the file to auto-populate a journal entry, distributing dollars to the processes where employees worked.

I've started a third tab labelled "Reg Earnings". In here I thought that I'd input a formula which would find the regular earnings dollars by employee number (Wk27), and prorate them to each process (Production Data) to calculate a fraction of total earnings by process. I'd repeat this by adding tabs for overtime, vacation, personal time etc. eventually distributing all payroll dollars.

There is one catch - sometimes there are manual payroll adjustments (highlighted in yellow on the Wk27 tab) where dollars hit, but no hours. The employee numbers are duplicated for these adjustments, but I'd have to handle them on a separate tab.

Thanks for the help!

[2709236]

I've tried to use multiple vlookup formulas with some success. Is this the only way to tackle this issue?