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.
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.