Filter an array

classic Classic list List threaded Threaded
2 messages Options
martin f krafft martin f krafft
Reply | Threaded
Open this post in threaded view
|

Filter an array

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,

--
@martinkrafft | <a href="https://matrix.to/#/#madduck:madduck.net">https://matrix.to/#/#madduck:madduck.net
 
"once ... in the wilds of afghanistan, i lost my corkscrew, and we
  were forced to live on nothing but food and water for days."
                               -- w. c. fields, "my little chickadee"
 
spamtraps: [hidden email]

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
martin f krafft martin f krafft
Reply | Threaded
Open this post in threaded view
|

Re: Filter an array

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?

--
@martinkrafft | <a href="https://matrix.to/#/#madduck:madduck.net">https://matrix.to/#/#madduck:madduck.net
 
a common mistake that people make
when trying to design something completely foolproof
was to underestimate the ingenuity of complete fools.
                                  -- douglas adams, "mostly harmless"
 
spamtraps: [hidden email]

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy