+ Reply to Thread
Results 1 to 5 of 5

Extract a list from data in sheet1 to sheet2 filtered by a starting date and ending date?

  1. #1
    Registered User
    Join Date
    01-17-2013
    Location
    Mauritius
    MS-Off Ver
    Excel 2003
    Posts
    7

    Extract a list from data in sheet1 to sheet2 filtered by a starting date and ending date?

    Salut Master of Excel

    I am looking for a formula which extracts a list from data in sheet1 to sheet2.

    In sheet1 I have data in range B2:F1000 with in column A dates.
    In sheet2 I have in A1 a date '01.01.2012' and in A2 date '01.06.2012'.

    Now in sheet2 I want to have a list in range B2:F1000 with extracted data from sheet1.B1:F1000 which is from date sheet1.A1 to sheet1.B1.

    I hope my info is clear enough.

    Is this possible? Thank you for your time and information.
    Last edited by vivace; 01-17-2013 at 08:31 AM. Reason: #sorry Pete, wrong input :/ my bad.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,777

    Re: Extract a list from data in sheet1 to sheet2 filtered by a starting date and ending da

    Yes it is possible. Do you have a header row for your data in Sheet1, so the actual data starts in row 2 ? If not I would suggest you insert a new row 1 and put appropriate header descriptions in the cells, and if you have them there you can put this formula in G2 of Sheet1:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and then copy this down to beyond your data to allow for future expansion - the hyphens will help to show you how far you have copied it.

    Then put your headers in row 2 of Sheet2, and put this formula in A3 of Sheet2:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and put this formula in B3:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The formula in B3 can then be copied across into C3:F3, and then each cell formatted appropriately. Then you can copy the row of formulae from row 3 down into row 4 downwards as far as you think you need.

    Then you can just change the dates in A1 and B1 to get another set of data displayed.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    01-17-2013
    Location
    Mauritius
    MS-Off Ver
    Excel 2003
    Posts
    7

    Thumbs up Re: Extract a list from data in sheet1 to sheet2 filtered by a starting date and ending da

    Hi Pete,

    thank you for your prompt answer. I have tried the formulas you provided and none of them worked. First of all it has to do that I forgot to mention in my registration at this forum that I use Openoffice CALC. Sorry about that. So I tried to changed the formulas into CALC layout, 2 formulas failed nevertheless; only the first formula works.

    After some research I came on the following formulas which work.

    Sheet1.G2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Sheet2.A3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Sheet2.B3: (Same as Sheet2.A3 but selected different column)
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    My formulas for A3 & B3 are probably longer then really necessary. Anyways, they work.

    Thank you for your help Pete, much appreciated!

  4. #4
    Registered User
    Join Date
    01-17-2013
    Location
    Mauritius
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Extract a list from data in sheet1 to sheet2 filtered by a starting date and ending da

    Aiii, I found a bug in the formula I produced. If the data in sheet1 is sorted by date, then there is no problem. However, if I have all the dates in sheet1 mixed, the formula produces not all the entries within range of the dates.

    Is your formula resistant to this bug? If so, do you know the Openoffice CALC version of the formula?

  5. #5
    Registered User
    Join Date
    01-17-2013
    Location
    Mauritius
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Extract a list from data in sheet1 to sheet2 filtered by a starting date and ending da

    Aiii, I found a bug in the formula I produced. If the data in sheet1 is sorted by date, then there is no problem. However, if I have all the dates in sheet1 mixed, the formula produces not all the entries within range of the dates.

    Is your formula resistant to this bug? If so, do you know the Openoffice CALC version of the formula?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1