Excel data query

Posted on 2011-10-09
Last Modified: 2012-05-12
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?
Question by:bryanscott53
    LVL 50

    Expert Comment

    by:barry houdini
    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

    Author Comment

    Hi Barry the dates are sorted and there is no repeats, I would prefer formulas if possible
    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


    This "array formula" in A4


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

    and in B4 this formula copied across and down


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

    regards, barry

    Author Closing Comment

    perfect, thank you!

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    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.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now