Need to Display Entire Row on Another Sheet by Date

[rtroehrig]

I need to be able to copy all rows on Sheet 1 in which today's date exists in Column C onto Sheet 2 and pack them at the top. The dates in Column C do not run in order as the "due dates" vary depending on the project, and this is where I run into a problem. The functions I've used in the past for this get buggy when the dates in the column are out of order.

For example, this works great when pulling a range of dates that are entered in order, but not so much if they are out of order:

{=(COUNT(Grease!E:E)+1)-COUNT(IF((INT(INDIRECT("Grease!E2:E"&MATCH(10^308,Grease!E:E)))>=INT(B1))*(INT(INDIRECT("Grease!E2:E"&MATCH(10^308,Grease!E:E)))<=INT(B2)),INDIRECT("Grease!L2:L"&MATCH(10^308,Grease!E:E))))}

Coupled with:

=IF(INDEX(Grease!A:A,$C$1+ROWS($A$4:A4))=0,"",INDEX(Grease!A:A,$C$1+ROWS($A$4:A4)))

Any ideas? Basically I want whatever is due for today, and today only, to display on Sheet 2.

 

[Fluff]

How about
=FILTER(Grease!A2:P1000,Grease!C2:C1000=TODAY(),"Nothing")

 

[rtroehrig]

I was overthinking it. I got it to work with the formula below. Just copy down and across and change cell references as needed:

=IFERROR(INDEX(Sheet4!$A:$A,AGGREGATE(15,6,ROW(Sheet4!$H:$H)/(Sheet4!$H:$H=TODAY()),ROWS(A$3:A3))),"")

 

[rtroehrig]

Ok new problem, I didn't realize they were using a calculation to determine the date that needs to be referenced. That throws a kink into this.

The entry sheet looks something like this:

Playground.xlsm
	A	B	C	D	E	F	G	H	I
1	Lot Number	Product Code	Product Name	Date & Time In	Test	Test Time	Date & Time Out	Results	Initials
2	123456	101010	Product A	5/5/20 10:08 AM	Test 2	20.5	5/6/20 6:38 AM	265	RR
3	123456	101010	Product A	5/5/20 10:08 AM	Test 3	100.25	5/9/20 2:23 PM	265	RR
4	123456	101010	Product A	5/5/20 10:08 AM	Test 1	2.75	5/5/20 12:53 PM	265	RR
5	123456	101010	Product A	5/5/20 10:08 AM	Test 1	2.75	5/5/20 12:53 PM	265	RR
6	123456	101010	Product A	5/5/20 10:08 AM	Test 1	2.75	5/5/20 12:53 PM	265	RR
7	123456	101010	Product A	5/5/20 10:08 AM	Test 4	225	5/14/20 7:08 PM	265	RR
8	123456	101010	Product A	5/5/20 10:08 AM	Test 1	2.75	5/5/20 12:53 PM	265	RR
9	123456	101010	Product A	5/5/20 10:08 AM	Test 5	6.5	5/5/20 4:38 PM	265	RR
10	123456	101010	Product A	5/5/20 10:08 AM	Test 1	2.75	5/5/20 12:53 PM	265	RR
Sheet4

Cell Formulas
Range	Formula
G2:G10	G2	=D2+(F2/24)

I'm just blanking on how to get the =Today() in my previously posted formula to read the Date & Time Out column properly. And yes, I have adjusted the H in the previous formula to G.

 

[rtroehrig]

I'm still trying to figure this one out if anybody has any ideas.

 

[Fluff]

Did my suggestion work for your initial question?

 

[rtroehrig]

Did my suggestion work for your initial question?

It's kicking back with "That function isn't valid."

I would imagine I'd still run into the same issue with the reference to =TODAY() since the column that I'm trying to reference contains a cell formula.

Sheet 4 is posted above and then Sheet 5 is currently running:
=IFERROR(INDEX(Sheet4!$G:$G,AGGREGATE(15,6,ROW(Sheet4!$G$2:$G$1000)/(Sheet4!$G$2:$G$1000=TODAY()),ROWS(G$2:G2))),"")

If I type today's date into column G, it works perfectly. But it won't pull the cells in which G is set by the cell formula.

 

[Fluff]

It would have nice if you could have told me that at the time, rather than just ignoring me.

Try
=IFERROR(INDEX(Sheet4!$G:$G,AGGREGATE(15,6,ROW(Sheet4!$G$2:$G$1000)/(int(Sheet4!$G$2:$G$1000)=TODAY()),ROWS(G$2:G2))),"")

 

[rtroehrig]

It would have nice if you could have told me that at the time, rather than just ignoring me.

Try
=IFERROR(INDEX(Sheet4!$G:$G,AGGREGATE(15,6,ROW(Sheet4!$G$2:$G$1000)/(int(Sheet4!$G$2:$G$1000)=TODAY()),ROWS(G$2:G2))),"")

Sorry about that. I got overly excited thinking I had it figured out and neglected to respond to your post.

I was clearly overthinking it again as I tried numerous things and never thought about something as simple as using the integer function.

This looks to have solved my problem. Your assistance is much appreciated, as always.

 

[Fluff]

You're welcome & thanks for the feedback.