Extract data between two dates

**grey_hair** · 04-19-2011, 11:40 AM

I have a long list (3 cols) - date, value, remark. I’d like to extract data between two dates in a separate table in another sheet. There is Start date and End Date. Every time I change start and end date, a table to be formed in other sheet. What formula to use? Example attached
Thanks in advance

**NBVC** · 04-19-2011, 11:51 AM

Assuming your start and end dates are in Sheet1, A2:B2, then in Sheet2, D2 add a helper column with formula:

=IF(AND(A2>=Sheet2!$A$2,A2<=Sheet2!$B$2),COUNT(D$1:D1)+1,"")

copied down. This indexes the matching items.

in E2 enter:

=MAX(D:D)

this gives count of matches.

Now in Sheet1, C2 enter:

=IF(ROWS($A$1:$A1)>Sheet1!$E$2,"",INDEX(Sheet1!A:A,MATCH(ROWS($A$1:$A1),Sheet1!$D:$D)))

copied down as far as you need and across the 3 columns.

**grey_hair** · 04-20-2011, 11:24 PM

Soory, but I'm comimng back with my problem.
The formulas in the given solution work only in certain specific cases-not for all. For instance everything is OK for the period 1.4.2001-7.4.2001, but that is all. No solution for other periods - say 1.4.2001-4.5.2002. In the Sheet1 correctly 5 records are shown but in Sheet2 something goes wrong-no require table is written. Same for other random periods.
I did exactly everything shown in given solution bot not result.
So please help me again
Sample attached
Thanks in advance for your suggestions ot different solution