Excel data query

In sheet 2 I have all my dates in column A and in columns b to k I have data. In sheet 1 a user selects a start date and an end date. I am able to select the start date data using a simple vlookup but what I am struggling with is how to pick up all the other rows of data after the start date up to the end date. Can someone help me with this bit?
bryanscott53Asked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
OK, assuming a small sample of 20 rows of data in Sheet2!A2:K20 then you could use this approach in sheet1.

Start date entered in A2, end date in B2

This formula in C2 to get the number of rows that should be returned

=COUNTIF(Sheet2!A2:A20,">="&A2)-COUNTIF(Sheet2!A2:A20,">"&B2)

This "array formula" in A4

=IF(ROWS(A$4:A4)>C$2,"",SMALL(IF(Sheet2!A$2:A$20>=A$2,IF(Sheet2!A$2:A$20<=B$2,Sheet2!A$2:A$20)),ROWS(A$4:A4)))

confirmed with CTRL+SHIFT+ENTER and copied down as far as required

and in B4 this formula copied across and down

=IF($A4="","",INDEX(Sheet2!B$2:B$20,MATCH($A4,Sheet2!$A$2:$A$20,0)))

see attached, try changing the date range to see what happens

regards, barry
27387715.xls
0
 
barry houdiniCommented:
Would it perhaps be simpler to use Advanced Filter?

You can use formulas - if you want though. Are the dates in column A sorted, do any dates repeat?

regards, barry
0
 
bryanscott53Author Commented:
Hi Barry the dates are sorted and there is no repeats, I would prefer formulas if possible
0
 
bryanscott53Author Commented:
perfect, thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.