Import Range of cells as per today date in another sheet

Question

There are three columns A, B, C AND today date is in the first row of the column A means A1. I need to import these three columns date excluding date row. A2:A100, B2:B100, C2:C100 in another sheet.

I have tried this:

=filter((importrange("sheets key", sheet1!A:F),
 importrange("sheets key"sheet2!A2:C10 =TODAY()))

My formula got an error cannot solve this.

Answer 1

FILTER require that the first argument is a valid range reference, so it's not valid to use IMPORTRANGE or another function as the first argument. There are several alternatives like using FILTER in the source spreadsheet the on the destination spreadsheet use IMPORTRANGE to import the range that holds the FILTER result.

Another alternative is to use QUERY instead of FILTER.

Answer 2

• sample it out and then use IMPORTRANGE to extract it out

=ARRAYFORMULA(INDIRECT("Sheet1!"&
 ADDRESS(2,  MAX(IF(Sheet1!1:1=TODAY(), COLUMN(Sheet1!1:1), )), 4)&":"&
 ADDRESS(10, MAX(IF(Sheet1!1:1=TODAY(), COLUMN(Sheet1!1:1), ))+2, 4)))

---