+ Reply to Thread
Results 1 to 9 of 9

Distribute the Multiple Invoices of one contract based on Start Date and End Date

  1. #1
    Forum Contributor
    Join Date
    08-19-2009
    Location
    UAE
    MS-Off Ver
    Excel 2016
    Posts
    167

    Distribute the Multiple Invoices of one contract based on Start Date and End Date

    Hi All,

    I have a list of invoices, some are for same contract but for different months, now these amounts should be distribute to second sheet based on contract and based on month and year.

    Any help appreciated.

    Thanks
    Jude
    Attached Files Attached Files

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Distribute the Multiple Invoices of one contract based on Start Date and End Date

    I went with E4:

    =IF(E$3=MEDIAN(E$3,VLOOKUP($A4,Prepaid!$A$7:$H$12,7,0),VLOOKUP($A4,Prepaid!$A$7:$H$12,8,0)),"X","")

    and copied it down and over
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Forum Contributor
    Join Date
    08-19-2009
    Location
    UAE
    MS-Off Ver
    Excel 2016
    Posts
    167

    Re: Distribute the Multiple Invoices of one contract based on Start Date and End Date

    Hi Daffodill,

    I need to distribute the amount to the second sheet, it should lookup all the invoices and the start date, end date and to distribute the result in second sheet. I attached the result in second sheet as an example.

    Thanks
    Jude
    Attached Files Attached Files

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Distribute the Multiple Invoices of one contract based on Start Date and End Date

    In your actual file:
    * Are the IDs integers: 1,2,3,...?
    * Are there gaps (or over lap) of days of a specific ID among previous End date and next Start date? i.e: ID 1 start 1-Feb, end 30-Jun; start 5-Jul, end Dec-31.
    Quang PT

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Distribute the Multiple Invoices of one contract based on Start Date and End Date

    See attachment.

    Distribution value for start, end date is base on actual days of the month.

    I used define name Start and End.

    Hope it works.
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    603

    Re: Distribute the Multiple Invoices of one contract based on Start Date and End Date

    Hi
    Maybe this? e4 cell =
    =SUMIFS(Prepaid!$I:$I,Prepaid!$B:$B,'Contract List'!$B4,Prepaid!$C:$C,'Contract List'!$C4,Prepaid!$D:$D,$D4)/12
    Appreciate the help? CLICK *

  7. #7
    Forum Contributor
    Join Date
    08-19-2009
    Location
    UAE
    MS-Off Ver
    Excel 2016
    Posts
    167

    Re: Distribute the Multiple Invoices of one contract based on Start Date and End Date

    Dear Bebo,

    I dont have words to say, Thank you very much, it works great. Its kind of project i am doing it, I am trying to create a Prepaid Amoritization, once done i will publish it in this forum, you can have look into it and you can give your feedback us well.

    Have a great day.
    Regards
    Jude

  8. #8
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Distribute the Multiple Invoices of one contract based on Start Date and End Date

    Nice to hear that.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  9. #9
    Forum Contributor
    Join Date
    08-19-2009
    Location
    UAE
    MS-Off Ver
    Excel 2016
    Posts
    167

    Re: Distribute the Multiple Invoices of one contract based on Start Date and End Date

    Hi Bebo,

    When the invoice is not received in one month that month amount should be zero, but the formula is calculating till end of the period.
    for example if the invoice received for 01-01-2012 till 31-01-2012 1000 and the second invoice received for 01-03-2012 till 31-03-2012 then
    Jan -1000
    Feb -0
    March 1000

    Any advise much apprciated.
    Thanks
    Jude

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Calculate next due date based on start date frequency and current date
    By ironoverload in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-09-2014, 07:08 AM
  2. Replies: 0
    Last Post: 03-21-2013, 12:22 PM
  3. Replies: 3
    Last Post: 10-23-2012, 09:04 PM
  4. Replies: 0
    Last Post: 07-03-2012, 03:52 PM
  5. Replies: 4
    Last Post: 06-02-2012, 11:26 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