Filter an array

users
#1

Hey,

I'd like to create a sheet that is essentially just an array reference to another spreadsheet, but filtered by a column. I.e. make this sheet list `{=file://./other_sheet.ods.A1:J150}` but only rows where the field in column C is TRUE.

I know I can filter the view of such a sheet, but I need to actually have only these rows show up, so I can use it in formulae.

Is this possible?

Thanks,

#2

Regarding the following, written by "martin f krafft" on 2021-03-10 at 09:32 Uhr +1300:

I'd like to create a sheet that is essentially just an array reference to another spreadsheet, but filtered by a column. I.e. make this sheet list `{=file://./other_sheet.ods.A1:J150}` but only rows where the field in column C is TRUE.

So I found that

{=IF('./other_sheed.ods'#Sheet1.C1:C150 = TRUE;
      './other_sheed.ods'#Sheet1.A1:J150; "")}

kinda works, but it includes empty rows for the rows not matching the condition, which is kind of obvious.

Short of writing a script to could be used to (re-)generate such a sheet in question at certain times, is there another way to avoid the empty rows?