Hello Forum members,

I have a question on making a formula with real time date function. I have built my own simple personal money management worksheet. I want to know how I can write a formula which can automatically change 1. update my monthly EMI under my monthly expenses column 2. update my cumulative mortgage amount from my total debt column, every month by the 15th for the next 5 years. I have this data for the month date, cumulative mortgage and the corresponding monthly EMI as an amortization table inside the sheet and now I am manually doing it

In the meantime I have uploaded A tex excel file in Google Docs. Here is the link for it.

https://docs.google.com/open?id=0B4X...Fppcnh6RExMbFk

I hope you are able to understand what I am looking for from the file. Briefly, In that file the required fields in red color ( Date, Mortgage, Debt) need to be updated automatically with the values of EMI ( B 15 - B 30) and Outstanding (C 15- C 30) from the sample Amortization table (A-C 15; A-C 30) given below every month on the 15th. i.e when the system date (F1) = corresponding date (A 15- A30) in the Amortization table.

I want the data in Fields E4 and K4 in the excel file get automatically updated 15th of EVERY month ( when both the values of F1 and corresponding value of A17-A30 match ) with the corresponding values of "EMI" and "Outstanding" in the table (A17 -C 30) below in the Test excel sheet. Means on the 15th of each month the 2 values in red get updated and remains the same till the next month 15th when again it gets updated and so on


What I kind of need in real logic terms is a formula which can do the following in the test sheet:

IF Date(F1) (05/15/2012) = Date (A1)(05/15/2012) then Mortgage (E4) = EMI (B1) and Debt (K4) = Outstanding (C1) and like that 15th of the next month the sheet should update automatic i.e., IF Date(F1)(06/15/2012) = Date (A2) (06/15/2012) then Mortgage (E4) = EMI (B2) and Debt (K4) = Outstanding (C2) and so.

I posted this question in another forum and a person gratefully helped me partly by giving me this solution.

E4: =IfError(INDEX(B17:B30,MATCH(F1,A17:A30,0)),"Bad date")
K4: =Iferror(INDEX(C17:C30,MATCH(F1,A17:A30,0)),"Bad date")

The above formulas works like a charm FOR THAT PARTICULAR DAY 15 FOR ANY MONTH / YEAR. However problem is before or after that day. For e.g. as the date today is May 18 its showing "bad date". So whats the way to work around that and keep the original current month data till the next 15th when the formula would automatically update it ? Ca the above formulas be modified to do that ?

Sorry for the trouble.


I hope my question though a bit long is clear and specific.

Thanks in advance for the help.

An Excel Learner