I have an interesting problem I cant figure out! I have a file which might make it easier but I will try to explain.
I have 4 worksheets in one workbook. Datasheet, Homeloans, YR2, Table.
On Datasheet in M2 I have a Date formatted as d-mmm-yy
On Homeloans I have Dates from A1:L1 which are derived from the date on Datasheet =DATE(YEAR(Datasheet!$M$2),MONTH(Datasheet!$M$2)+0,1) that progress up to =DATE(YEAR(Datasheet!$M$2),MONTH(Datasheet!$M$2)+11,1) in L1
In A2:L2 I have values entered
On YR2 I have the same setup. The date system is setup to look like a fiscal year instead of a calendar year.
On the Table sheet from B1:Y1 I have dates. B1 uses the formula =IF(Datasheet!M2<>"",TEXT(Datasheet!M2,"d-mmm-yy"),"Jan") B2 uses =IF(OR(B$1="",B$1=""),"",TEXT(EDATE(B$1,1),"d-mmm-yy")) and is propagated to Y1.
In B2:Y2 I am using this formula =SUMPRODUCT(--(TEXT(Homeloans!$A$1:$L$1,"d-mmm-yy")=B$1),Homeloans!$A$2:$L$2) with it changing respectively to Y2
This is the tricky part.
On Table, Cell C18 I have a data validation list setup to select a year number
On Table, E18 I have formula =SUMPRODUCT(--(RIGHT(TEXT($B$1:$Y$1,"d-mmm-yy"),1)+0=$C$18),$B$2:$Y$2)
Here is the problem.
If I set the date on Datasheet to 1/11/2011 and I select year one on C18 on the Table sheet, I get the correct sum on E18.
If I change the date on Datasheet to 1/11/2012 it flips the value in E18 to zero. This is the weird part. If I change the value in C18 to 2 for second year, then it gives me the correct sum but it should be for year 1. So I played with the formula
=SUMPRODUCT(--(RIGHT(TEXT($B$1:$Y$1,"d-mmm-yy"),1)+0=$C$18),$B$2:$Y$2) and found that if I change the +0 part to +-1 then select year one again, it gives me the correct sum.
It sort of does what I need it to do but the problem is for every date set I will have to go in and change that value up and down the number line according to the date on Datasheet and that just isnt going to work. It need to be dynamic and pull values based on the year the values show up in.
So if a payment is entered for example on 1/11/11 as the first payment and the second payment on 1/12/11, and I want to know the totals for the first year of the loan, it should only give me the value for those two dates in E18.
Any help in resolving this would be greatly appreciated. Im running out of forehead to bang against the wall!