• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

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?
0
bryanscott53
Asked:
bryanscott53
  • 2
  • 2
1 Solution
 
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
 
barry houdiniCommented:
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
 
bryanscott53Author Commented:
perfect, thank you!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now