• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

How to dynamically match and sum values in two seperate tables

The following table is a summary of wages paid to an employee compared to wages they are actually entitled to be paid.

The user makes selections on the left hand side of the spreadsheet based upon when they worked and any penalties, allowances or other payments they were entitled to over each pay period.

These selections are then summarised in the “Pay Period-Sub Totals” section (columns AC-AJ).

The user is then able to manually enter data for “Wages Paid by Employer” (columns AL-AT)

The drop down lists will provide the same options to types of payment but the amounts may be different.

The next section “Variances” (columns AU-BC) needs to be able to match each payment type entered into the previous two sections and then provide the values of any underpayments and overpayments.

For example, the solution would need to match any Wages Paid  “Base Rate” amount (in this case in cell AM13) and subtract the Entitlements “Base Rate” amount (in this case cell AD12).
If there is no match for a value in “Wages paid” (such as “Salary Only” in the attached example in AL12:AM12) then the sum would be 900-0.

Currently I have set up a formula based solution (seen in cells AV2:AV8) which uses vlookup to search for matches and then subtracts the actual entitlements from what was actually paid. For my crude solution I have only applied it to the first pay period as an example.

Looking for a solution that is capable of matching like for like and working out the differences for each pay period (the pay periods are 5 days in this case, but could be weekly, monthly, fortnightly or some other custom number of days <32).

Any ideas for solutions would be great!
 Variance-solution-mock-up.xlsx
0
Andross9
Asked:
Andross9
1 Solution
 
Nico BontenbalCommented:
I think the SumIf function would make your calculations a lot simpler. Enter this formula in cell AV12:
=AM12-SUMIF(AC$12:AC$16;AU12;AD$12:AD$16)
And then copy it down to AV16. You'll see you still have the same results. But now you still need different formula's for different ranges. You could add hidden columns that combine the value of Intrument Code (column A) with the Type (column AC and AU). Then you can use the same principle (sumif) on the whole range from row 12 to 41.
0
 
Andross9Author Commented:
Will need a further solution for populating in each pay period automatically but this will be another question, not directly asked for in this one.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now