Data lookup based upon date range

[WORBY10]

I'm hoping someone can help me with a formula for the below example sheet please. I'm looking to create a report that shows specific data based upon a date range - what formula would I use for this? The ID number denotes the line on the data tab which needs to be looked at. I have put examples of the info I would expect to see when the date ranges changed.

If you need anymore info please let me know.

Thanks, James

Test Document.xlsx
	A	B	C	D	E
1
2	From Date	01/01/2024
3	To Date	31/09/2024
4
5	ID	V1 COMMENTS	REJECTION REASON	V2 COMMENTS	REJECTION REASON
6	555624
7
8
9
10
11	Expected results
12	Date range set to 01/01/2024 to 20/09/2024
13	ID	V1 COMMENTS	REJECTION REASON	V2 COMMENTS	REJECTION REASON
14	555624	Test comment	Incorrect Colour
15
16
17	Expected results
18	Date range set to 01/01/2024 to 31/09/2024
19	ID	V1 COMMENTS	REJECTION REASON	V2 COMMENTS	REJECTION REASON
20	555624	Test comment	Incorrect Colour	Test comment number 2	Wrong Shape
21
22
23	Expected results
24	Date range set to 21/09/2024 to 31/09/2024
25	ID	V1 COMMENTS	REJECTION REASON	V2 COMMENTS	REJECTION REASON
26	555624			Test comment number 2	Wrong Shape
Report

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E25	Cell Value	=0	text	NO
E19	Cell Value	=0	text	NO
E13	Cell Value	=0	text	NO
E5	Cell Value	=0	text	NO
C1:C10,C13:C16,C19:C22,C25:C1048576	Cell Value	=0	text	NO

Main Sheet

Test Document.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	zDate	ID	Pack_Format	Online_Notes_Stage1_Comments	Online_Notes_Stage1_Approval Edited	Online_Notes_Stage1_Rejection Type Edited	Online_Notes_Stage1_Rejection Type Edited	Online_Notes_Stage1_Timestamp	Online_Notes_Stage2_Comments	Online_Notes_Stage1_Approval Edited	Online_Notes_Stage2_Rejection Type Edited	Online_Notes_Stage1_Rejection Type Edited	Online_Notes_Stage1_Timestamp
2	19/09/2024	555624	Base	Test comment		Incorrect Colour		19/09/2024 10:34:44	Test comment number 2		Wrong Shape		25/09/2024 13:34:44
Data

Data sheet

 

[Vogateer]

You have to adjust the dates in these DATE() formulas manually unless you decide to place the dates in separate cells where they're easier to reference. You'll also need to adjust the data! ranges to match the number of rows you have, and I count at least 4 of them.

B14:

=LET(ID_row,FILTER(data!$A$2:$M$2,(data!$B$2=$A14)*(data!$H$2>=DATE(2024,1,1))*(data!$H$2<=DATE(2024,9,20)),""),IFERROR(CHOOSECOLS(ID_row,4,6), ""))

D14:

=LET(ID_row,FILTER(data!$A$2:$M$2,(data!$B$2=$A14)*(data!$M$2>=DATE(2024,1,1))*(data!$M$2<=DATE(2024,9,20)),""),IFERROR(CHOOSECOLS(ID_row,9,11), ""))

Copy those down to the rows below, adjust the dates, and I think it might work.

 

[bebo021999]

On a side note regarding your request:
It seems that cell B3 ('To Date') does not contain an actual date, as it displays an invalid date '31-Sept' (September only has 30 days).
Therefore, it might be text, and Excel will interpret it as the next day, '1-Oct', which could lead to incorrect results.
Try to format the cell containing the date as 'Date' to avoid unnecessary errors