Search code examples
excelgoogle-sheetsgoogle-sheets-formulavlookupgoogle-query-language

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

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: enter image description here

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 enter image description here

Any idea what formule can i use combined with IMPORTRANGE?

Best regards.

Solution

  • 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")&"'", )