Excel - find cells in a column that add up to a certain amount

[MJamison07]

I have listing of inv #'s and amounts. Ocassionally, I receive a payment (via direct deposit) without a listing of invoices that are paid. Eventually the client sends the voucher, but sometimes I need to know sooner.

Is there a way I can get excel to search the amount column and find the invoice amounts that add up to the payment?

For example:

Inv 1 $2
Inv 2 $4
Inv 3 $6

Payment amount is $8. Can excel find the invoices that add up to $8?

Thanks!
Martha

 

[mscallisto]

how will you manage invoices that have many items that add up to the amount you're looking for or are you certain this will never happen?

 

[MJamison07]

While I will never say never, I am 99% certain this will never happen. What kind of a problem would that create if it did?

 

[SkipVought]

Hi,

Why is it even an issue to "match up" a payment with specific invoices? Do you never get "Payment on account" payments? Would you apply a payment to an invoice, leaving an older invoice uncovered?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GlennUK]

A way of doing it is shown here:

faq68-4742

... but I don't know if it does exactly what you want.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[macropod]

See also:

http://www.tek-tips.com/viewthread.cfm?qid=1485698

Cheers
[MS MVP - Word]

 

[MJamison07]

Thank you all for your responses.
Glenn I started with your suggestion. I tested it by selecting a few invoices and used the sum of those as the target. I would enter that target into the solver. Sometimes it worked, sometimes it timed out and returned decimals in the "b" column. There are no duplicate invoice amounts. Any ideas as to why it didn't work every time?

Thanks.
Martha

 

[Gavona]

I haven't tried the solution but the FAQ does say

add the constraint that B1:B30 must be 'bin' (Means binary as in 1 or 0, and it's one of the dropdowns, so just hit the arrow and select 'bin')

Is it possible that you missed this step?

Gavin

 

[xlhelp]

Hi, Martha.

There is also a solution by Tushar Mehta that includes an Excel template with an example. I have tried it and it's quite slick.

http://www.tushar-mehta.com/excel/templates/match_values/

Canadian eh! Check out the new social forum Tek-Tips in Canada.
I should live a long time - I eat a lot of preservatives.