Closed Thread
Results 1 to 10 of 10

Multiple invoices and payments from same supplier, data track

  1. #1
    Registered User
    Join Date
    05-05-2014
    Location
    romania
    MS-Off Ver
    Excel 2007
    Posts
    6

    Multiple invoices and payments from same supplier, data track

    Hi,
    please help, I'm stucked...

    I have a supplier that send me multiple invoices to be paid, at different dates.
    Need to track the payments (as amounts) and in a separate page to see the invoices and if the payments closes them.
    On columns from Sheet1 i also have some other informations, used in other sheets !!!!

    On column A = date of invoice
    Column B = invoice value
    column C = date of payment (partial payment, or maybe more money, in this case must allocate themselves to next invoice)
    Column D= amount paid

    In Sheet 2 i have to recover from sheet1 only the invoices and payments:
    column A = date of invoice
    Column B = invoice value
    Column C = date of payment
    Column D = amount paid
    Column E = message if the invoice is totally paid, or partially paid
    One cell in column F = TOTAL Difference remaining to be paid for all invoices

    I do not know how to allocate from amount paid the money to 2 invoices for example:

    invoice 1 = 1000
    invoice 2 = 1400

    first payment = 600 (and shows me that invoice 1 is partially paid, invoice 2 is not paid)
    second payment = 700 (and automatically allocate rest for payment at invoice 1, and remaining is putted at invoice 2) ---this i do NOT know how to do it...

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Multiple invoices and payments from same supplier, data track

    Probably not too difficult. But we need something to work with.

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the sample data, replace any personal info with random junk of the same text style.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    05-05-2014
    Location
    romania
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Multiple invoices and payments from same supplier, data track

    Hello,
    Thanks, i attache an example with description...i have no ideea how to solve it...
    Appreciate the help.
    Attached Files Attached Files

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Multiple invoices and payments from same supplier, data track

    I split your invoices and payments onto two separate sheets.

    You don't really need to list unpaid invoices out separately, you already have the invoices listed on the first sheet. I've added a new column with a formula that tracks the payments on the second sheet and applies the payments directly to the data rows. You can see at a glance which invoices have unpaid balances.

    The second sheet lists all your payments and has a snapshot summary of each group's remaining debt.

    Don't see any real need in this project for VBA.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-05-2014
    Location
    romania
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Multiple invoices and payments from same supplier, data track

    Waw...i have thought at a lot of VBA and missed the simple one
    Thank you.
    Best regards

  6. #6
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Multiple invoices and payments from same supplier, data track

    I've marked this thread as SOLVED for you.
    Next time, select Thread Tools from the links above to mark a thread as SOLVED. Thanks.

  7. #7
    Registered User
    Join Date
    10-16-2020
    Location
    Mississippi
    MS-Off Ver
    360
    Posts
    3

    Re: Multiple invoices and payments from same supplier, data track

    I need to do basically the same thing so how do I copy the formulas from the sample above. however, in my instance I don't have specific invoice names, just amounts and payments. I have 38 invoices and maybe 10 or so payments, but the payments only have to be allocated to the group so I can tell which are closed and which are still open. I have 38 invoices and maybe 10 or so payments. Any assistance you can offer is appreciated.

    Any assistance you can offer is appreciated. I have a group of can I just click and drag a handle. I can also sub out your numbers, but not sure how to handle naming them unless I give them all the same name.




    Quote Originally Posted by JBeaucaire View Post
    I split your invoices and payments onto two separate sheets.

    You don't really need to list unpaid invoices out separately, you already have the invoices listed on the first sheet. I've added a new column with a formula that tracks the payments on the second sheet and applies the payments directly to the data rows. You can see at a glance which invoices have unpaid balances.

    The second sheet lists all your payments and has a snapshot summary of each group's remaining debt.





    Don't see any real need in this project for VBA.
    Last edited by kingspointer; 10-16-2020 at 03:48 PM.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,504

    Re: Multiple invoices and payments from same supplier, data track

    Quote Originally Posted by kingspointer View Post
    I need to do basically the same thing so how do I copy the formulas from the sample above. however, in my instance I don't have specific invoice names, just amounts and payments. I have 38 invoices and maybe 10 or so payments, but the payments only have to be allocated to the group so I can tell which are closed and which are still open. I have 38 invoices and maybe 10 or so payments. Any assistance you can offer is appreciated.

    Any assistance you can offer is appreciated. I have a group of can I just click and drag a handle. I can also sub out your numbers, but not sure how to handle naming them unless I give them all the same name.
    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  9. #9
    Registered User
    Join Date
    04-13-2021
    Location
    Nairobi, Kenya
    MS-Off Ver
    MS Office 2013
    Posts
    1

    Thumbs up Re: Multiple invoices and payments from same supplier, data track

    Hi,

    I really need to learn about tracking multiple payments for multiple invoices.

    Kindly assist.

    Kind Regards

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    55,019

    Re: Multiple invoices and payments from same supplier, data track

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Posting Maltiple Payments to Multiple Invoices – FIFO Method
    By amardas in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-17-2013, 12:40 AM
  2. Replies: 0
    Last Post: 01-14-2013, 01:44 PM
  3. Replies: 3
    Last Post: 01-03-2013, 10:42 AM
  4. Replies: 2
    Last Post: 07-08-2009, 05:45 PM
  5. [SOLVED] Matching cheque payments to invoices
    By Eddie in forum Excel General
    Replies: 6
    Last Post: 05-18-2005, 02:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1