Returning a mortgage balance based on a particular date
It should be a simple lookup. Although most people would create a columnar
table, it appears you are doing it in rows. So you would use Hlookup. Something
like:
=hlookup(today(),sheet1!4:5,2,true)
--
Regards,
Fred
"JWH" wrote in message
...
I have created a simple worksheet to calculate the monthly balance of a fixed
rate mortgage loan (assumes fixed payments each month).
For example, in worksheet 1:
A4 = Dec. 2006 and C4 = balance as of Dec. 2006
A5 = Jan. 2007 and C5 = balance as of Jan. 2007 after monthly interest is
added and one monthly payment has been made.
I want to create a formula in a cell (G4) of a seperate worksheet (work
sheet 2) that will reference the data in worksheet 1 and display the
balance of the loan based on the current date. So if the current date is
December 11, 2006 (today), G4 in worksheet 2 will display the balance
calculated in worksheet 1 for each successive month. So for example, when I
open the workbook on January 3, 2007, the forumula in G4 of work sheet 2 will
reference the current date (January 3, 2007) and display the loan balance in
C5 workbook 1 that has been calculated for Jan. 2007 (A5) .
I'm open to a simpler solution if I not going about this correctly.
Regards and Thanks in advance.
JWH
|