Looks like I need some help, please:
I have a column (A1:A12) of dates representing the twelve months of a
company year (e.g. A1 = April 2007 to A12 = March 2008).
In the second column (B1:B12) is a 'cumulative amount due' currency figure
representing the sum to date of the monthly expenses claimed by an employee
over the company year.
The third column (C1:C20) contains the various dates that expenses were
reimbursed to the employee.
The fourth column (D1-D20) contains a record of the expense amounts
reimbursed to the employee over the year. These reimbursements are random
and not necessarily on a monthly basis. There might be more than one
reimbursement in a month, or there might not be any. The number of
reimbursements could exceed twelve in the year, hence using D1:D20 for this
column.
In the fifth column (E1:E20) I want to show the 'balance of expenses due'
(or overpaid) to the employee at the date the reimbursement was made.
What I would like to do is calculate the 'balance of expenses due' based on
the date that the reimbursement was made (C1:C20), using the 'cumulative
amount due' figure (B1:B12) for the same year & month.
Example: If a reimbursement was made on 18 June 2007 (assume reimbursement
date entered in cell C4 and reimbursement amount entered in cell D4) , then
the amount reimbursed to date (sum cells D1:D4) should be subtracted from
the 'cumulative amount due' figure in column B1:B12 that corresponds to June
2007 (June 2007 would be in cell A3 and the corresponding 'cumulative amount
due' would be in cell B3).
Therefore the sum would be (B3-SUM(D1:D4)). The answer should appear in
cell E4
How easy is it to compare the year and month of the reimbursement date with
the dates in column A1:A12, find the year/month match and use the
corresponding 'cumulative amount due' in the calculation?
Is this a task for VLOOKUP? I've tried VLOOPUP, but keep getting a #REF!
error.
Thanks
--
Mike
-Please remove 'safetycatch' from email address before firing off your
reply-