Try this. Insert this is cell A2 of Sheet2:
=query('Sheet 1'!A2:K337,"Select * limit "& (weeknum(today(),1)*4))
It uses the Limit clause to display four days for each week of the calendar year.
EDIT:
4 days per "day"
Try this. Insert this is cell A2 of Sheet2:
=query('Sheet 1'!A2:K337,"Select * limit "& mod(days(today(),date(year(today()),1,1)),84)*4)
This solution also uses the "Limit clause", but the key is in the use of mod
to calculate the number of days to display.
date(year(today()),1,1)
- this returns the starting date in the calendar year. Note the use of year(today())
- this ensures that the starting date will automatically roll over to the start of the following calendar year.
days(today(),date(year(today()),1,1))
- this calculates the number of days between "today" and the first day of the calendar year.
mod(days(today(),date(year(today()),1,1)),84)
- "mod" returns the remainder of a division calculation. In this case, the number of elapsed days is divided by 84. The value of "84" is derived by taking the total number of data-days (336) divided by 4 (display 4 rows per day) - this result is the number of days it takes to get to the bottom of the list. So "mod(days,84)" returns the number of days in the next annual cycle.
- The solution requires that 4 days of data is displayed for each days of the year. So, the formula multiplies the mod result by 4.