Aligning Dates in order with payments

[MarkDShark]

I need a formula that can list dates in order on another sheet as I enter them on the entry sheet. Im trying to create a spreadsheet that tracks payment dates plus the amount as it subtracts out the balance. Sort of like the example below. I have the first part deducting as I input, but what I want to do, is to be able to enter all the dates no matter what order they are in, but as I do, I need the 2nd spreadsheet to put them in order in a certain format, example below of the entry form, of coarse it should have the lines etc.

Date of loan: 04-15-2010 End Date: 04-15-2015
Balance $4,100.00 ($150.60)

No DATE AMOUNT
1 7/17/2013 $376.65
2 8/21/2013 $376.65
3 9/18/2013 $376.65
4 4/15/2013 $1,949.00
5 11/20/2013 $376.65
6 12/18/2013 $409.65
7 2/19/2014 $385.35

I would need the next sheet to put them in order from earliest to the latest as well as the corresponding payment starting with the "Date of loan" date and ending with the "End Date". So in this example, No4 should show first on the next sheet as I type in the dates at random on the input sheet. Outcome should be in following format below,

04152010 04152013 4,100.00 <-- (Starting with the Date of loan, then the April payment date then the balance)
04152013 07172013 2,151.00 <-- (Carrying down April date, subtracting April payment $ entered on the input sheet, reflecting new balance)
07172013 08212013 1,774.35 <-- (Carrying down next date completing process till all payments entered
08212013 09182013 1,397.70
09182013 11202013 1,021.05
11202013 12182013 644.40
12182013 02192014 234.75
02192014 04152015 -150.60 <-- (Last payment date as well as the "End date". Balance was paid 02192014.

The format is sort of in a zig zag order but needs to be for the sheet im trying to create. Hope you understand it.

 

[MarkDShark]

Well as in my original post, it does reflect the original bal loan date etc. Yes I would have a payment history sheet, that just lists all payments coming in and ones transferred in from other accounts, so the dates don't always show in numerical order. They will reflect the original date it was made and also reflect the date it was transferred into the balance. It would be up to me to input them in order and so on. On the sheet I am creating does reflect a balance due box, that does update as I enter the payments and dates, the only thing im missing, is the formula on the 2nd sheet that would put them in order for me in the listed format. The zig zag format is how I described it. That format is a reflection of the way the dates are entered on the program that computes the time frames etc. But that program is a pain to use because I have to view the list of payments, and find the earliest one, plug it in and then the next one etc etc. Once all dates are entered, the program then calculates the end results

 

[oldbrewer]

so why not just sort by date ascending and customer name ascending, before you do anything with the sheet

 

[MarkDShark]

I thought about that, but it doesn't calculate on my entry sheet how I have it. Right now as I enter the info, its deducts the payment out the bal due and updates it. I have the original bal due box right next to the adjusted bal due box so I can see them both, the original amount as well as the adjustments to it. The actions that ascending does is to put things in order, that's sort of what I need on the 2nd sheet, within the zigzag format while also calculating the balance due as I input the info on the 1st sheet.

 

[oldbrewer]

I meant on sheet 2 ...............

 

[MarkDShark]

Well what I was hoping to create was a procedure that did all that on its own. The 2nd sheet would sort the date, payments etc all within the format described. The only thing Id want to do on the 2nd sheet is to just view it and cut and paste the results.

 

[oldbrewer]

you have the entry sheet which details all the loan and repayment details and you want the second sheet to be one row for each loan, giving the current status, ie amount borrowed, timescale, payments made to date and future payments due, with some tweaking of the interest rate. The rules of this forum do not allow offline work normally, but until I see your sheet 1 I cannot grasp this. PM me with your Email and we go off line for a while, and then come back on here with the methodology and formulas adopted.

 

[MarkDShark]

I was off for a few days. Let me put together a rough draft the basic frame work of what it should look like and hopefully you can get a better idea of what im trying to do. Basically I just need the 2nd sheet to produce the format for cut and paste purposes. Will get back with you when I have it completed.