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

Brecht

Member
Joined
Sep 15, 2014
Messages
36
Reaction score
0
Points
6
Location
Netherlands
Excel Version(s)
Version 2007
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.
 
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.
 
Hello,

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

Attachments

  • Sample sheet.xlsx
    11.1 KB · Views: 10
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.
 
Sorry, here you are.
 

Attachments

  • Sample sheet.xlsx
    11.6 KB · Views: 13
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
 
Yes, unfortunately I do. BT, BU, BV need to stay different sheets.
 
Back
Top