Mar 09 2022 03:29 PM
If anyone can help - I have a large formula that works for the cell intended. I need the same formula in different cells, but with a different look up range. Editing the formula range does not change the value. I have tried Control-Shift-Enter and it does nothing. Please help! The formula is :
=SUM(INDIRECT(ADDRESS(MATCH(A4,Transpose!$A$4:$A$55,0)-3,19,1,TRUE,"Transpose")):INDEX(INDIRECT(ADDRESS(MATCH(A4,Transpose!$A$4:$A$55,0)-3,19,1,TRUE,"Transpose")):INDIRECT(ADDRESS(MATCH(A4,Transpose!$A$4:$A$55,0)+3,19,1,TRUE,"Transpose")),7))
Changing the range (A4:A55) inside the formula doesn't change the value.
Mar 09 2022 03:59 PM
Mar 09 2022 08:04 PM
Thank you for your help. I am attaching an example. I need the journal entries to autofill based on the date of the journal entry, with the sum of the date of the entry and prior 6 months. The data is in the Summary tab, but I have also transposed it (Transpose tab) - the range can come from either tab. However, each line on the JE refers to a different range, which I have color coded the first few as an example. !st attachment is JE I need formula, second is part of the summary sheet (horizontal) and 3rd is the Summary sheet Transposed. Any help is appreciated!
Mar 10 2022 09:20 AM
@Kerry_83 I don't have the sheet to test on but I think a better approach would be:
=SUMIFS(Transpose!$S$1:$S$1000, Transpose!$A$1:$A$1000,"<="&EOMONTH(A4,0), Transpose!$A$1:$A$1000,">="&EOMONTH(A4,-6), Transpose!$B$1:$B$1000,"Interest")
That said I have a couple of notes:
this assumes column A dates are actually dates and not TEXT that just looks like a date
that you actually do want last 6 months of values (including all of the month for the given date as I did here or should it only be <= that exact date). Part of this question is because I noticed a couple of the months had an ADDED entry. is that why the previous included 7 entries instead of 6? Were you forced into 6month in order to guarantee a consistent number? With this formula you can choose the time period.
To change from Interest to Cash Int or Accrued Int just change the "Interest" accordingly.