Autofilling spreadsheet row when date is (ex: today) and value "name" is in that row-CodePudding

i'm trying to get some info from another spreadsheet using IMPORTRANGE and VLOOKUP.

I have created a new spreadsheet and named it after a date of the month ex: 21 February In another sheet, i have a big list of dates where each date has a phone number, a name and a persone who this "contact" has been asigned.

I want that whenever this list of dates is updated, they get sent into different spreadsheets.

Example:

In the other spreadsheet i want to automaticall fill in the sheet based on the DATE which on the example above is 21.

So, Toms sheet should look like

Any idea what formule can i use combined with IMPORTRANGE?

Best regards.

CodePudding user response：

first, run IMPORTRANGE as standalone formula to connect your sheets by allowing access

next, try in Tom sheet:

=QUERY({IMPORTRANGE("url_or_id_of_sheet", "sheet_name!A:D")}, 
 "select Col2,Col3 
   where Col4 = 'Tom' 
     and Col1 date = '"&TEXT(TODAY(), "yyyy-mm-dd")&"'", )

CodePudding user response：

=QUERY({IMPORTRANGE("ID OF SHEET", "SHEET_NAME!A:I")}, "select Col3,Col4 where Col8 = 'Tom' and Col2 = 'date as text'",)

This worked for me as where i have placed date, it's a text cell not a date.

Thanks to @player0