Hi, I am trying to figure out a formula to keep my series of dates dynamic.
This is what I have so far.
My first date is pulled like this into the table: =IF(Datasheet!M2<>"",TEXT(Datasheet!M2,"mmm-yy"),"Jan") The cell below it is like this:
=SUMPRODUCT(--(TEXT(Homeloans!$A$1:$L$1,"mmm-yy")=B$1),Homeloans!$A$2:$L$2)
This works. It pulls the value I am looking for from Homeloans into the Table worksheet.
The second date formula creates the date following the result of first formula date and it is causing the problem.
Its formula is =DATE(YEAR(Datasheet!$M$2),MONTH(Datasheet!$M$2)+1,1) and the formula below it is;
=SUMPRODUCT(--(TEXT(Homeloans!$A$1:$L$1,"mmm-yy")=C$1),Homeloans!$A$2:$L$2)
How should the second date formula be written so that it follows the first date in the series, stays dynamic, but produces the same result?
Thanks,
Risk