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 D120 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 D14) 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(D14)). 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-

Hello!

i didn't quite understood your explanation, but may I suggest combining the
Vlookup and the formula month. sorry I cant be of greater help but i you
play a little with the formulas ull get it right!

"mlv" <(E-Mail Removed)> escribió en el mensaje de
noticias:fre0mr$hf$(E-Mail Removed)...
> 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 D120 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 D14) 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(D14)). 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-
>

"mlv" <(E-Mail Removed)> wrote in
news:fre0mr$hf$(E-Mail Removed):

> 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
> D120 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 D14)
> 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(D14)). 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

it would have helped to see your vlookup to see what the problem was, but
vlookup should work
copy & paste this in cell e4

=VLOOKUP(C4,$A$1:$B$12,2)-SUM($D$14)

then you can copy& paste up and down column E

pub wrote:
>
> it would have helped to see your vlookup to see what the problem
> was, but vlookup should work copy & paste this in cell e4
>
> =VLOOKUP(C4,$A$1:$B$12,2)-SUM($D$14)
>
> then you can copy& paste up and down column E

Actually my VLOOKUP formula wouldn't have helped you much at all because to
write the original question with any clarity, I had to greatly simplify the
cell references. Even then, it was difficult for someone else to follow
what I was trying to do. My actual table contains many merged cells.

The good news is that having studied your example, I could see where I had
gone wrong. I had specified the table incorrectly.

My final formula (which appears to work perfectly) is:

=IF(ISERROR(VLOOKUP(L6,$C$6:$J$29,8)-SUM($N$6:O6)),"",VLOOKUP(L6,$C$6:$J$29,8)-SUM($N$6:O6))

I've used the ISERROR function to hide the error message when there are rows
with only partial information entered.

Thanks for your help.
--
Mike
-Please remove 'safetycatch' from email address before firing off your
reply-

"mlv" <(E-Mail Removed)> wrote in
news:frm0fc$7p7$(E-Mail Removed):

> pub wrote:
>>
>> it would have helped to see your vlookup to see what the problem
>> was, but vlookup should work copy & paste this in cell e4
>>
>> =VLOOKUP(C4,$A$1:$B$12,2)-SUM($D$14)
>>
>> then you can copy& paste up and down column E
>
> Actually my VLOOKUP formula wouldn't have helped you much at all
> because to write the original question with any clarity, I had to
> greatly simplify the cell references. Even then, it was difficult for
> someone else to follow what I was trying to do. My actual table
> contains many merged cells.
>
> The good news is that having studied your example, I could see where I
> had gone wrong. I had specified the table incorrectly.
>
> My final formula (which appears to work perfectly) is:
>
> =IF(ISERROR(VLOOKUP(L6,$C$6:$J$29,8)-SUM($N$6:O6)),"",VLOOKUP(L6,$C$6:$
> J$29,8)-SUM($N$6:O6))
>
> I've used the ISERROR function to hide the error message when there
> are rows with only partial information entered.
>
> Thanks for your help.

good that it worked. i think you were right, and your description
without the formula turned out to be the best way. the mere mention of
"merged cells" would have had me running for the hills )