Solved

# Excel data query

Posted on 2011-10-09
177 Views
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
Question by:bryanscott53

LVL 50

Expert Comment

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

Author Comment

Hi Barry the dates are sorted and there is no repeats, I would prefer formulas if possible
0

LVL 50

Accepted Solution

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

Author Closing Comment

perfect, thank you!
0

## Featured Post

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.