How to dynamically match and sum values in two seperate tables

Posted on 2011-05-04
Last Modified: 2012-05-11
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!
Question by:Andross9
    LVL 22

    Accepted Solution

    I think the SumIf function would make your calculations a lot simpler. Enter this formula in cell AV12:
    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.

    Author Closing Comment

    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.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now