Posts by riskier4ra

    I made the changes you suggested and E18 just remained zero.


    I messed with it some more and I got it to work with your updated formula. Not sure why it didnt work the first time.


    That way just does what it use to do without having to change +0. Your first proposal works fine. I will just have to make the Data Validation List Range a lot larger.


    Your second proposal =SUMPRODUCT((YEAR($B$1:$Y$1)=--("200"&$C$18))*$B$2:$Y$2), simply looks at the first three characters of the year and adds the value in C18 to it for it to find the values to sum. So what happens is if I change the year to 2002 on the datasheet, and I select 1 in C18 I get nothing, but If I select 2, then I get the values for 2002, however the number 2 that I select in C18 is supposed to represent the number of years into the loan so I should be able to select 1 and get the values for year 2002.


    To further explain, lets say you started a loan in 2006, but you are in 2018 and you want to pull the expenses for 2018, I would like to select the value 12 because its 12 years into the loan. This way I only need to use 1-30 for the data validation list name range.

    I modified almost all formulas in your file.
    See if it is what you want, in attach


    riskier4ra test.xlsx


    P.S. Why you change date in text then change back?


    Hi Tom,


    The date change was probably when I was testing it while waiting for some help. Sorry for any confusion. Yes that is what I want to happen but would prefer to be able to select by year number of the loan instead of an actual year (if possible), and it be relative to the dates in the table from least to greatest, which should rely on the date on datasheet. So for example. I started a loan in 2002, and its 2018, I want to be able to select the year I am in of the loan which would be 16. That way I have a generic data validation list that is based on the maximum number of years of a standard homeloan which is 30 here. This will prevent me from having to update the name range to include years that a loan may be in. Not sure if that is possible with excel. I understand why you made the changes you did, and why the DVL is by year now instead of number, but I do not know if excel is limited in that capacity or if there is another way to make what I need to happen happen. I have to admit I am not an excel guy. Systems administration, Im good. Knowing the full extent of Excel's capabilities, not so much. I do appreciate your help and time, I just need it to work with a number and not a specific year, if possible.

    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!


    Regards,
    D

    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. 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 highlighted 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 that value in E18.


    Any help in resolving this would be greatly appreciated. Im running out of forehead to bang against the wall!


    Regards,
    D

    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