Excel Help?

mrb82

Hi MIPs!

Do any of you know of a fomula that would look at a list of values (in this case currency) and calculate which of these values will make a given figure?

For example, I receive, say £1,500, from a debtor, but this figure is made up of many smaller varying figures relating to various invoices. I ant to be able to input the received amount that I want to make up, and get the formula to look at a column with the invoice values and see which of these values will make the total I'm looking for.

Does this make sense, should I post this to the student area?

Many thanks if you can help!

JodieR

I don't know how to write a formula to do this, but what I do when i'm trying to match payments with multiple invoices is list all the invoices in one column in excel and then in the next column enter the payment as a negative number. then click on the payment, hold down the ctrl key and click on the invoices which you think it could relate to. If you look at the bottom ribbon towards the right you'll see the sum of the highlighted cells so keep clicking on invoices until the sum is nil. You can obviously keep trying different combinations until you get it to zero, and if you've got serveral payments to match up, once I've got one matched up I colour the cells of the payment and invoices one colour so I know not to use them again. Hope this helps. On some older versions of excel it may not automatically show the sum on the bottom ribbon but if you right click on it there's usually an option to turn it on.

Jodie

AK002

Don't think there is a formula for this (or it'll be very complicated), can you just not as your customers to drop you a note of the invoices they are paying? Or ask them to submit remittance advices?

JodieR

AK002 wrote: »

can you just not as your customers to drop you a note of the invoices they are paying? Or ask them to submit remittance advices?

And where's the fun in that?! I often find its quicker and easier to work it out myself than to request and wait for a remittance advice.

mrb82

JodieR wrote: »

And where's the fun in that?! I often find its quicker and easier to work it out myself than to request and wait for a remittance advice.

This is true. While they do send a remit, it's not until later on in the day, even if I'm straight on the phone asking for it. My cash team require a breakdown by claim number (invoice number) of the total cash receipt so they can import it on to our system.

I currently go through my list that I know is outstanding and ctrl click combinations and let the autosum show me what's what, but it doesn't always work. It's tricky too when there are ~30 claims (invoices) and they're paying 7!

Thanks for the replies though!

blobbyh

Though I've never personally used it, could Goal Seek possibly cope with this (though the query might even be too complex for that)?

mrb82

I'll give that a go (on Wednesday, of course!).

(hanks for the suggestion and have a great Christmas!