Data Extraction from another Sheet based on Date

[chrisandsally]

Hi All

Please see attached sheet.

Sheet 1 called Summary requires 1 Input "The Date" Example 12/03/2021
Sheet 2 Called Data is where the information to be extracted is held.

In the example sheet, the date of the 12/03/2021 has been selected in the summary sheet, and believe I need an array formulae (trying to avoid VBA, but if need be, will explore) to extract all rows that have that date along with the data fields.

Highlighted is the expected result

Can you help?

[Pete_UK]

Here's a simple way, which uses a helper column in the Data sheet. Put this formula in cell A5 of the Data sheet:

=IF(B5=Summary!$C$4,MAX(A$4:A4)+1,"-")

Copy this down as far as you like (to at least cover the data that you have) - the hyphens will show where the formula is active. Then in the Summary sheet you can use this formula in cell B9:

=IFERROR(VLOOKUP(ROWS($1:1),Data!$A:$H,COLUMNS($A:B),0),"")

Copy this across to H9, then copy those formulae down until you start to get blanks. When you change the date in C4 the display will automatically update.

Hope this helps.

Pete

[chrisandsally]

Dear Pete_UK Thank you for your very quick reply. I will mirror on real dataset. Thanks

[chrisandsally]

Dear Pete_UK

I would like to change the input date cell to =NOW() to always show todays date, even when formatted identically it does not work. Do you why and how to fix?

Thank you

[Bo_Ry]

Now is date and time
Better use =today()

and for MS 365 try at B9

=FILTER(Data!B5:H99,Data!B5:B99=C4)

[ChemistB]

The NOW() Function includes the time. Formatting the cell differently does not remove the time, it only hides it. Use TODAY() instead

[chrisandsally]

Thank you Bo_Ry and ChemistB for the TODAY() function. Completely forgot about that. Awesome Thanks Again.

[chrisandsally]

Dear All, This post has been marked as resolved but I have one more addition, If I need to create a new thread let me know. Based on Bo_Ry answer. I wish to add a check box or value that I mark against the Summary Sheet that will update the Data Sheet.

The real life scenario is that you can enter a date in Summary and it populates the summary sheet. That now works, I wish the Check box to mark the individual lines off that then updated the data sheet. If a new date is entered then it reflects the current value in Data.

I have updated the file to explain what i'm after

Thank you