excelexcel-formulaautopopulate

How do i pull data from one worksheet to another where a column includes a specific date?

I am struggling with an Excel Spreadsheet and hopefully someone can help.

It has two worksheets, "Database" and "Printsheet"

In the database, there are several columns, A:A contains an unique number, B:B contains dates and C:C Contains names.

Printsheet has a cell to put a date in (A:2) and a table beneath.

The idea is that the table in the "Printsheet" will auto populate with all rows from "Database" that contain the date entered to be able to print off.

I have tried allsorts, there is no "Filter" formula available in my version of excel which a lot online seems to suggest.

So far i seem to be able to bring all values from database but they do not filter out the other dates. Can anyone help please?

It is something like :-

=IFERROR(INDEX(Database!B:B,MATCH(A2),Database!$A:$A,0)),"")

for column A of the Table etc etc but it doesnt seem to work. Any help appreciated

    =IFERROR(INDEX(Database!A$2:A$200,
  SMALL(IF(Database!B$2:B$200=A2,ROW(Database!A$2:A$200)-ROW(Database!A$2)+1),
  ROWS(Database!A$2:A2))),"")

This formula just brings up all unique numbers regardless of the date in column Database!B:B

Database

# Trip Date Location Name
1 04/12/2023 Chatsworth Amanda M
2 05/11/2023 Newark Amanda P
3 04/12/2023 Chatsworth Andy C
4 04/12/2023 Chatsworth Andy F
5 04/12/2023 Chatsworth Andy F
6 04/12/2023 Chatsworth Anita B.

Printsheet
04/12/2023
Chatsworth
# Date Name Contact Telephone Number P-up Location Deposit Paid In Full
1
2
3
4
Solution

  • Or, you could try using the AGGREGATE( ) function as well, instead of SMALL()

    enter image description here

    • Formula used in cell F3

    =IFERROR(INDEX($A$2:$D$7,AGGREGATE(15,6,(ROW($B$2:$B$7)-ROW($B$2)+1)/($F$1=$B$2:$B$7),ROW(A1)),{1,2,4}),"")

    Change the ranges and references as per your suit.

    Or, make it bit dynamic by adding a function called MATCH() for the column index, also change the date to trip date.

    enter image description here

    • Formula used in cell F3

    =IFERROR(INDEX($A$2:$D$7,
  AGGREGATE(15,6,(ROW($B$2:$B$7)-ROW($B$2)+1)/($F$1=$B$2:$B$7),ROWS(F$3:F3)),
  MATCH(F$2,$A$1:$D$1,0)),"")

    Fill down and fill across!