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
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
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!
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
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
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.
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 *
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
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.
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks