Array Formula to find rows on another sheet dated within the last 7 days-CodePudding

I have rows of data on Sheet 1 ("Base Stats") with Col A containing the date the row of data was inserted. There is a header row spanning Col A to Col I. First row of data is Row 2 and so on.

I have a Sheet 2 ("Latest") which replicates the header row of Sheet 1. I am trying to use an ArrayFormula on Sheet 2 to output rows which are dated (have been entered) within the last 7 days of TODAY().

Sheet 2 Cell K2 contains =TODAY(), L2 contains =TODAY()-7 and M2 contains =COUNTIF('Sheet 1'!A:A, ">"&TODAY()-7). I have tried the following in Cell A1 on Sheet 2:

={"Date Added";ARRAY_CONSTRAIN(ARRAYFORMULA(IF(ROWS(A$2:A2)>$M$2,"",INDEX('Base Stats'!$A$2:$A,SMALL(IF('Base Stats'!$A$2:$A>=$L$2,IF('Base Stats'!$A$2:$A<=$K$2,ROW('Base Stats'!$A$2:$A)-ROW('Base Stats'!$A$2) 1)),ROWS(A$2:A2))))), 1, 1)}

This works, but only populates Row 2 A2 on Sheet 2 and does not continue A3, A4 etc.

What am I doing wrong!? Thanks in advance.

https://docs.google.com/spreadsheets/d/1VzHY8fTq8OsXhpHYHESSSPxeVNOnqxpjcsyWJpbuEOs/edit?usp=sharing

CodePudding user response：

Delete everything from the "Latest" sheet (you don't need any of it, including your formulas in K, L, M).

Then place the following formula in A1:

=ArrayFormula({'Base Stats'!A1:I1; SORT(FILTER('Base Stats'!A2:I,'Base Stats'!A2:A>=TODAY()-7),1,0)})

This will produce all headers and all results from the last seven days, with the most recent up top. If you want the most recent at the bottom but still want them sorted, just change the final 0 in the formula to 1.