Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

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
0
binaryman101
Asked:
binaryman101
  • 7
  • 4
1 Solution
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
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
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now