Hi
(On fly)
Let you have a sheet Data with a table (headers in row 1): Date, Time, Temp
Add a column (p.e. RepRow) to left your table (column A now, and you can
hide this column later)
Define named range
DataDate=OFFSET(Data!$B$1,1,,COUNT(Data!$B:$B),1)
Add a sheet Periods
On sheet Periods, create a table Day, Month, [Year], with according headings
in row 1.
Into cell Periods!A2 enter the start date.
Periods!A3=IF($A$2+ROW()-2>TODAY(),"",$A$2+ROW()-2)
Copy Periods!A3 down as long as is reasonable for you.
Periods!B2=IF(DATE(YEAR($A$2),MONTH($A$2)+ROW()-1,0)>TODAY(),"",DATE(YEAR($A$2),MONTH($A$2)+ROW()-2,1))
Format Periods!B2 as "yyyy.mmmm" or "yyyy.mm", and copy also down as long as
reasonable (but ~30 times less rows as column Day)
When you have data for different years in same table too, then you'll need
Year column too
Periods!C2=IF(YEAR($A$2)+ROW()-2>YEAR(TODAY()),"",YEAR($A$2)+ROW()-2)
Format as General and copy down for some rows
Define a couple of named ranges
PeriodList=OFFSET(Periods!$A$1,,,1,COUNTA(Periods!$1:$1))
DayList=OFFSET(Periods!$A$1,1,,COUNT(Periods!$A:$A),1)
MonthList=OFFSET(Periods!$A$1,1,1,COUNT(Periods!$B:$B),1)
[YearList=OFFSET(Periods!$A$1,1,2,COUNT(Periods!$C:$C),1)]
Add a sheet Report.
On sheet Report in row 1:
Into one cell in enter "Period:", for another (it may be next one, but also
further to right - it depends on width of your report columns you'll design
later) apply data validation list with source "Day", "Month" [; "Year"].
Define the cell with data validation as named range Period.
Further to right, for some some cell apply data validation list with source
=IF(Period="Day",DayList,IF(Period="Month",MonthList,""))
(When you use period Year too, add an IF-level for it too)
Define this cell as named range Selection
On sheet Data (here the formula when you use "Day" and "Month" as periods
only - when you use "Year" too, the formula will be more complex)
Data!A2=IF(AND($B2<>"",IF(Period="Day",$B2=Selection,DATE(YEAR($B2),MONTH($B2),1)=Selection),"",SUMPRODUCT(--($B$2:$B2)>=Selection),--($B$2:$B2)<=IF(Period="Day",Selection,INDEX(MonthList,MATCH(Selection,MonthList,0)+1,)))))
Copy the formula down at least for entire table. When all was right above,
you'll have numbered all rows in Data table, which match with conditions
estimated on Report sheet.
Now you have to create a report table on sheet report - using functions
ROW() to estimate report row number, and VLOOKUP(Data!$A?,...) to get
according data from sheet Data.
On Report sheet, you select period, and you get according report.
