Returning outstanding invoice amount for partial payments

[mehdi_154]

Hi!

I'm maintaining a simple spreadsheet for my business to account for all the goods that I receive on a monthly credit basis and make payments the following month. Now, all the payments that I make; are not always equivalent to the total amount of their invoice amount, but partial payments mostly - depending on the business available cash flow.

In this spreadsheet, I have the following worksheets:

1. (Accounts-Payable) Bill Entries - Used for entering new bills after receiving goods from the supplier
2. Bill Payments - Used for entering bill payments that have been made
3. Supplier Balance Summary - Provide the summary of total liabilities that the business owes
4. Vendor List - List of all the suppliers with their individual payment category (i.e. Cash On Delivery / Accounts Payable / Pre-paid Payments)

In this spreadsheet, a sheet named 'Bill Payments', I have been using a combination of INDEX and MATCH function (in Column F) to return the invoice "amount" from the sheet named '(Accounts-Payable) Bill Entries'; however, while making payments, in the sheet called 'Bill Payments', if the payment amount is not equal to the invoice amount (which is mainly less than the invoice amount), then the remaining outstanding amount reflects in 'Column G'. So, I wanted to get the outstanding amount to appear in 'Column F' from 'Column G' while making payment the second time in the sheet.

I'm using the below formula to match the invoice amount:

=IFERROR(INDEX('(Accounts-Payable) Bill Entries'!$E:$E,MATCH('Bill Payments'!B6&'Bill Payments'!D6,'(Accounts-Payable) Bill Entries'!$Z:$Z,0)),"")

Your helpful advice in solving the problem would be highly appreciated!

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

[mehdi_154]

Hi Ali, Thank you for your response! I've read the instructions to upload the excel file; however, the spreadsheet is a bit bigger in size because of the formulas it contains than that of the forum requirement, which is why I could not upload the file

[AliGW]

I didn't ask to see the real spreadsheet. The instructions say this:

... realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results.

Sorry, but I'm not going to waste time recreating what you've shown us in a picture when you could very easily provide us with a cut-down sample of it.

[mehdi_154]

Okay, understood! Here is the sample of the spreadsheet attached

[JeteMc]

Try pasting the following into cell F3 and then copying down to at least A7:

Please Login or Register  to view this content.

Let us know if you have any questions.

[mehdi_154]

It works like a beauty. Thanks!

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

[mehdi_154]

Thank You, I hope that you do as well!