filtering without empty criteria

Matthias Hiese

January 11, 2024 14:44

None

Hello,
I would like to filter all project expenses from the General Ledger table.
Before I do this, I want to create a table that only contains the project codes that are posted to in the general ledger. (simplified without date restriction)
To do this, I only filter for rows in the table that contain a project code. I use the filter criterion "<>@@" for this.

=NL("Filter"; "Line item"; "Project"; "Project no.";"<>@@")

Question 1: Is there a better criterion for excluding empty cells in general?

I would also like to exclude two special project codes (99I123 and 96A125)

Question 2: What would then be the correct formula?

=NL("Filter"; "G/L item"; "Project"; "Project no.";"<>@@&<>99I123&<>96A125") does not work.

Thank you.

CascadiaDataworks January 11, 2024 17:22

Usually the order of your filter values doesn't matter, except in this one case. If you want to say "these and/or blank" you HAVE to put the @@ at the end. Otherwise, @@ also represents allow special characters so when you put it as the first filter value, you are telling Jet to look for the literal value of '&<>99I123&<>96A125'

So your filter should be "<>99I123&<>96A125&<>@@"

Matthias Hiese January 12, 2024 07:21

perfect, thanks

filtering without empty criteria

2 comments