Populating information in a complicated way

I am attaching a spreadsheet that I am working on.  What I need to do is have all of the information from A17 through F17 in the Monthly report tab populated in the Summary of Payments tab in cells A4:F4 every time there is a payment.  Also, every time a new payment is made, that needs to populate one row down in the Summary of payments tab.  
For example: In the spreadsheet I am attaching two payments have been made.  The payment information for 10/5/2011 should populate in Cells A4:F4, and the information for the payment on 10/8/2011 should populate in cells A5:F5 in the Summary of Payments Tab. etc... This will continue for an entire year's worth of payments.
Thanks,
Eric
Finished-Amortization-Simple-Int.xls
binaryman101Asked:
Who is Participating?
 
redmondbCommented:
binaryman101,

OK, I found a much more efficient way and it doesn't rely on the check no. - so either payment type will match.

Please check that my understanding of the columns in the Summary sheet is correct.

Finally, I've changed formulas on both sheets so please re-paste all highlighted columns (and note that G1:G2 are no longer used in the Summary sheet.)

Regards,
Brian. Amortization-table-for-experts-e.xlsx
0
 
redmondbCommented:
Hi, binaryman101,

Please see attached.

The bits I've added to the two sheets are highlighted.

Note: It's a big file, so I deleted all the parts I wasn't using. So, you'll have to copy and paste my bits into your original.

Regards,
Brian.
Finished-Amortization-Simple-Int.xls
0
 
binaryman101Author Commented:
Well, this almost worked.  Oh, trust me I know it is a big file...I told you I was complicated...lol.  

I like the idea of tracking the number of the payment in another column, but I need that to be in the Loan data tab.  When I tried to copy/paste the your formula into the Loan Data tab, I noticed 2 things.  1) it made the payment numbers reverse...Check #12345 was payment 2 and #22222 was the first payment.  I'm not sure why they reversed.  The second issue was that the payment number did not include the principle only payment column.  We will at some point include principle only payments and that data needs to be included.  

Any ideas?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
redmondbCommented:
Barryman1,

Quick answer to your second point. I was falsely guided  by your column name, so that's a simple change. I'll look at the 1st one now.

BTW, regarding your other question, please see http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27391118.html#a36950970

Regards,
Brian.
0
 
redmondbCommented:
binaryman101,

Please see attached.

(1) I deleted half the rows in Loan Data.

(2) The file is xls, but it aleady had 2007 functionality in it, so I saved it as xlsx. Let me know if that's a problem!

Regards,
Brian. Finished-Amortization-Simple-Int.xlsx
0
 
binaryman101Author Commented:
There was no problem with the xlsx file you sent in terms of functionality.  Luckily my pc handled it..lol.

It corrected everything except for principle only payments.  I am attaching an example in the attached spreadsheet. I would try to explain, but I don't know where the error is in the logic.



Amortization-table-for-experts-e.xls
0
 
redmondbCommented:
binaryman101,

OK, I'm onto it. (5.8mb - you're determined to kill my mobile broadband link, aren't you?!)

Cheers,
Brian.
0
 
redmondbCommented:
bannerman101,

Yeah, I see the problem. I was trying to reduce the lookup load and over-finessed. Will a payment (whether principal-only or not) always have a check no.?

Thanks,
Brian.
0
 
binaryman101Author Commented:
lol, "over-finessed"  good times.  Yes, all payments will always have a check number with it.

0
 
binaryman101Author Commented:
All I can say is, you have earned your Guru status today.  Oh, and yes, I tried to kill your mobile link today muhahahaha.  

For real though, I very much appreciate your help.
Thanks,
Eric
0
 
redmondbCommented:
Eric,

A pleasure - I'm glad I finally for there!

Now, if I could just find my old V.90 modem...

Regards,
Brian.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.