Results 1 to 9 of 9

Thread: If row A contains certain date, row B in sheet2 copies data from row in Sheet1

  1. #1
    Acolyte Brecht's Avatar
    Join Date
    Sep 2014
    Location
    Netherlands
    Posts
    31
    Articles
    0

    If row A contains certain date, row B in sheet2 copies data from row in Sheet1



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,

    I have an Excel file containing various sheets, which I will call BT , BU , BV , plus Sheet2. Column A of sheets BT, BU, BV, usually contains a date, for example 18-07-2017 (DD-MM-YYYY).

    I am looking for a formula that does the following.
    In cell A1 of Sheet1, I insert a date, in our example, 18-07-2017 . This date changes a lot.
    In row B, I need a formula that searches whether the date of cell A1 is found in row A of sheets BT, BU, BV , and thereafter copies the data from that entire row. The same formula will be used in row B, C, etc. so that I will have a full overview of all the cells that contain this date.
    So far, I only came up with two formulas that lets me know whether the date is found in sheet BT:
    =IFERROR(INDEX(BT!A$2:A$10000;MATCH(A1;BT!A2:A10000;0));"")
    =IFERROR(VLOOKUP(A1;BT!A$2:A$10000;COLUMNS($A4:A10);FALSE);"")

    (The formulas are based on the explanation found on this link there is also an explanation on the COLUMNS-part of the VLOOKUP formula that I do not fully understand).

    Is what I described possible? If yes, how can I build this?

    I have not worked with macros a lot, the file will be used at least once every day, with varying dates, and the person using this file is not very good with computers, so I would prefer to do this with formulas.

    Thank you very much in advance.

  2. #2
    Super Moderator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    715
    Articles
    0
    This bit:

    COLUMNS($A4:A10)

    increments as you drag copy the formula down. As it stands here, it counts the number of columns between A4 and A10 (7) and returns that value as the column reference in the VLOOKUP formula. When you copy down to the next row it will return 8, as the range will have changed to A4:A11, and so on.

    Hope this helps!

    As for the rest, please attach a sample workbook.
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Acolyte Brecht's Avatar
    Join Date
    Sep 2014
    Location
    Netherlands
    Posts
    31
    Articles
    0
    Hello,

    apologies for the late reply, please see a sample sheet attached.
    Attached Files Attached Files

  4. #4
    Super Moderator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    715
    Articles
    0
    It's not clear what you want here. In your sample file, please manually mock up the results you want to see if the chosen date is 18 July 2017.
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #5
    Acolyte Brecht's Avatar
    Join Date
    Sep 2014
    Location
    Netherlands
    Posts
    31
    Articles
    0
    Sorry, here you are.
    Attached Files Attached Files

  6. #6
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,170
    Articles
    0
    Do you need to have different worksheets for this matter?
    One list, with an extra column containing BT BU ... on one sheet would be perfect for a Pivot Table.
    Your layout will be resorting to functions like INDIRECT which can be delicate to cope with
    Thank you Ken for this secure forum.

  7. #7
    Acolyte Brecht's Avatar
    Join Date
    Sep 2014
    Location
    Netherlands
    Posts
    31
    Articles
    0
    Yes, unfortunately I do. BT, BU, BV need to stay different sheets.

  8. #8
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,170
    Articles
    0
    Which XL version are you using?
    Thank you Ken for this secure forum.

  9. #9
    Acolyte Brecht's Avatar
    Join Date
    Sep 2014
    Location
    Netherlands
    Posts
    31
    Articles
    0
    Excel 2016

Posting Permissions

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